By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,651 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,951
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", "@@/@@/@@ @@@@")

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
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
Expert Mod 2.5K+
P: 3,485
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
Expert 100+
P: 1,107
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
Expert 100+
P: 1,107
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
Expert 2.5K+
P: 2,951
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
Expert Mod 2.5K+
P: 3,485
@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
Expert 100+
P: 1,107
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

Post your reply

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