473,324 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How do I convert a numeric string to a date value?

Seth Schrock
2,965 Expert 2GB
I am trying to import a text file into my database. One of the fields that I need is a Date/Time field in my database. My problem is that the value in the text file shows the date of 1/27/2015 15:32 as 0127151532. How do I convert this? I also need the 24 hour clock converted to a 12 hour clock at the same time.
Jan 28 '15 #1

✓ answered by jforbes

You might try using Format to instead of Mid$. Maybe something like this?:
Expand|Select|Wrap|Line Numbers
  1. format("0127151532", "@@/@@/@@ @@@@")

7 1171
Seth Schrock
2,965 Expert 2GB
I have found a possible solution using the DateSerial() and TimeSerial() functions and then use the CDate() function to convert the strings to a date. The problem is that I have to do a lot of Mid() functions to split out the pairs of numbers. If anyone has a better solution, I would love to hear it.
Jan 28 '15 #2
twinnyfo
3,653 Expert Mod 2GB
Seth,

The trick is to look at each portion of the text string as a particular part of the date/time.

So, 0127151532 becomes.....

Expand|Select|Wrap|Line Numbers
  1. Left(strDateTime, 2) = ' the Month
  2. Mid(strDateTime, 3, 2) = 'the day of the month
  3. Mid(strDateTime, 5, 2) = 'the Year
Adding "/" between those values and you can use the CDate() Function to convert into a Date Value.

Likewise....

Expand|Select|Wrap|Line Numbers
  1. Mid(strDateTime, 7, 2) = ' the Hour
  2. Mid(strDateTime, 9, 2) = 'the Minute
And then, adding ":" between the text values and using the CDate() function will generate a Time.

Or, you could put all the values together before applying the CDate() function.

Hope this hepps!
Jan 28 '15 #3
jforbes
1,107 Expert 1GB
You might try using Format to instead of Mid$. Maybe something like this?:
Expand|Select|Wrap|Line Numbers
  1. format("0127151532", "@@/@@/@@ @@@@")
Jan 28 '15 #4
jforbes
1,107 Expert 1GB
Oh, and I think you can use Mod to convert to 12 hour format:
Expand|Select|Wrap|Line Numbers
  1. (1532 mod 1200) and (332 mod 1200) both return 332
Jan 28 '15 #5
Seth Schrock
2,965 Expert 2GB
Thank-you jforbes. That is exactly what I need. Here is my final code to get it to work the way that I need it to.
Expand|Select|Wrap|Line Numbers
  1. CDate(Format("0127151532", "@@/@@/@@ @@:@@"))
Jan 28 '15 #6
twinnyfo
3,653 Expert Mod 2GB
@jforbes,

Cool! I was not aware one could apply formats to strings using the Format() Function like that. Another tool I can put in my tool kit!
Jan 28 '15 #7
jforbes
1,107 Expert 1GB
Glad it helps.

I was working on a custom formatting function recently that is somewhat related to this. The Format() will insert characters into a string, but I needed something to only put them in if they weren't there already. I made the following big o' mess to do just that. I though I would share incase you guys might want to use it in the future, plus I like to share =)
Expand|Select|Wrap|Line Numbers
  1. Public Function isCharAlphaNumeric(ByRef sString As String) As Boolean
  2.     isCharAlphaNumeric = (sString >= "A" And sString <= "Z") Or (sString >= "0" And sString <= "9")
  3. End Function
  4.  
  5. Public Function formatPartNumber(ByRef sString As String, ByRef sTemplate As String) As String
  6.  
  7.     ' Use >, <, or ^ as the first character for
  8.     '   > Uppercase
  9.     '   < Lowercase
  10.     '   ^ Camelcase (First letter of each word)
  11.     ' Use ~ for any AlphaNumeric
  12.     ' Use {} for an optional set where the start position is remembered and reset if a match doesn't occur
  13.     '       Helpful for when there are multiple options at the end of a string
  14.     ' Use [] for an optional set that doesn't reset the start postion
  15.     '
  16.     ' Examples
  17.     ' --------
  18.     ' formatPartNumber("aBCd-efg/ap", ">~~~~[-~~~]{/AP}{/S}") -> ABCD-EFG/AP
  19.     ' formatPartNumber("aBCdefg/sXYz", ">~~~~[-~~~]{/AP}{/S}[~][~][~]") -> ABCD-EFG/SXYZ
  20.     ' formatPartNumber("aBCd-efg", "<~~~~[-~~~]{/AP}") -> abcd-efg
  21.     ' formatPartNumber("aBCd-efgxyz", "^~~~~[-~~~]{/AP}") -> Abcd-efg
  22.  
  23.     Dim sResult As String
  24.     Dim sBuild As String
  25.     Dim iTemplate As Integer
  26.     Dim iString As Integer
  27.     Dim iFormat As Integer
  28.     Dim sCurrentTemplateChar As String
  29.     Dim iOptionalStringPos As Integer
  30.     Dim bOptional As Boolean
  31.     Dim iOptionalMatch As Integer
  32.     Dim sTemp As String
  33.  
  34.     iTemplate = 2
  35.     iString = 1
  36.     iFormat = 0
  37.  
  38.     Select Case Left(sTemplate, 1)
  39.         Case ">" ' Upper
  40.             iFormat = 1
  41.         Case "<" ' Lower
  42.             iFormat = 2
  43.         Case "^" ' Camel Case
  44.             iFormat = 3
  45.         Case Else ' None
  46.             iFormat = 0
  47.             iTemplate = 1
  48.     End Select
  49.  
  50.     Do While iTemplate <= Len(sTemplate)
  51.         sCurrentTemplateChar = Mid(sTemplate, iTemplate, 1)
  52.         Select Case sCurrentTemplateChar
  53.             Case "~" ' Character
  54.                 sTemp = Mid(sString, iString, 1)
  55.                 sBuild = sBuild & Mid(sString, iString, 1)
  56.                 iString = iString + 1
  57.                 If iOptionalMatch > 0 Then If Not isCharAlphaNumeric(sTemp) Then iOptionalMatch = 0
  58.             Case "{" ' Optional Match Start
  59.                 sResult = sResult & sBuild
  60.                 sBuild = ""
  61.                 bOptional = True
  62.                 iOptionalMatch = 1
  63.             Case "}" ' Optional Match End
  64.                 If iOptionalMatch > 0 Then
  65.                     sResult = sResult & sBuild
  66.                 Else
  67.                     iString = iOptionalStringPos  ' Backup position
  68.                 End If
  69.                 sBuild = ""
  70.                 iOptionalMatch = 0
  71.             Case "[" ' Optional Group Start
  72.                 sResult = sResult & sBuild
  73.                 sBuild = ""
  74.                 bOptional = True
  75.                 iOptionalMatch = 2
  76.             Case "]" ' Optional Group End
  77.                 If iOptionalMatch <> 0 Then sResult = sResult & sBuild
  78.                 sBuild = ""
  79.                 bOptional = False
  80.                 iOptionalMatch = 0
  81.             Case Else ' Other
  82.                 If bOptional Then
  83.                     Select Case iOptionalMatch
  84.                         Case 0
  85.                         Case 1
  86.                             If Mid(sString, iString, 1) <> sCurrentTemplateChar Then
  87.                                 iOptionalMatch = 0
  88.                             Else
  89.                                 iString = iString + 1
  90.                             End If
  91.                         Case 2
  92.                             sTemp = Mid(sString, iString, 1)
  93.                             If Len(sString) < iString Then iOptionalMatch = 0
  94.                             If sCurrentTemplateChar = "~" Or sTemp = sCurrentTemplateChar Then
  95.                                 iString = iString + 1
  96.                             End If
  97.                     End Select
  98.                 Else
  99.                     If Mid(sString, iString, 1) = sCurrentTemplateChar Then iString = iString + 1
  100.                 End If
  101.                 sBuild = sBuild & sCurrentTemplateChar
  102.         End Select
  103.         If Not bOptional Then iOptionalStringPos = iString
  104.         iTemplate = iTemplate + 1
  105.     Loop
  106.  
  107.     ' Select case, haha, get it
  108.     Select Case iFormat
  109.         Case 1
  110.             formatPartNumber = UCase(sResult)
  111.         Case 2
  112.             formatPartNumber = LCase(sResult)
  113.         Case 3
  114.             formatPartNumber = StrConv(sResult, 3)
  115.         Case Else
  116.             formatPartNumber = sResult
  117.     End Select
  118. End Function
  119.  
Jan 28 '15 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Nimmy | last post by:
Hi, I have a file which has different dates, I want to scanf them as CHAR and convert them to DATE format, how can I do this? Thanks
3
by: Convert TextBox.Text to Int32 Problem | last post by:
Need a little help here. I saw some related posts, so here goes... I have some textboxes which are designed for the user to enter a integer value. In "old school C" we just used the atoi function...
2
by: Remi Caron | last post by:
Hi, I took over an Visual Object project (Visual Clipper) in that language there is a function to: Convert a string containing a 32-bit binary date to a date data type. That function is called...
4
by: Ken Varn | last post by:
I have an unknown numeric Type object passed into a function. I want to run a conversion on a string to convert the string to that Type object and return an object of that type. Is there some way...
5
by: darrel | last post by:
I have the following right now to enter a date into SQL getting the data from some pull down menus: ------------------------------------------------- dim dateCCJApprovedDate as DateTime if...
1
by: abcabcabc | last post by:
I write an application which can let user define own date format to input, How to convert the date string to date value with end-user defined date format? Example, User Defined Date Format as...
1
by: John | last post by:
I have a drop down that is showing dates, I need to pass the selected date to my proc, so I pass it like this; ...
6
by: Aussie Rules | last post by:
Hi, I have a datepicker that show a calender. The user picks a date and the time component is always 00:00. I then have a drop down that provides a list of times, (10:00, 11:00 etc), and I...
6
by: Mark Chimes | last post by:
Hi All, I need to search thru some strings and discard them if they canot be converted to a decimal or interger value. What is the best way to do this? cheers, Mark Chimes
3
by: kronus | last post by:
I'm receiving an xml file that has a child called modified and it represents a date value in the form of a string -- Nov 14, 2008 -- and in my app, I have items associated with each object and I'm...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.