Converting a string to date/time

[RayMilhon]

I have a large text file to be imported into Access 2010. Several of the fields are date/time fields. The text file formatting of the date/time is as follows:

20120412092134

I can take this string and convert it so that it looks like this

04/12/2012 09:21:34

but when I use any of the builtin conversion functions I normally use all I actually get is the date the time gets stripped off. Is there a builtin conversion function for this that I'm not finding or do I need to build one myself?

[RayMilhon]

Never mind right after I posted this I retried the CVDate function and it worked as desired. realized when I tried it before I had a typo and that's what caused it not to work.

[pbaldy]

Check out the DateSerial() and TimeSerial() functions, using Mid() to pull out the appropriate parts. Adding the results together should get you the complete date/time.

[pbaldy]

Oops, too slow. Didn't realize CDate() could handle that string.

[RayMilhon]

Not CDATE CVDate Used the Mid Function to break the string down to the individual components then used CVDate with the components in the right order to translate back to a date time.

Here's the function
the original string is 20120412092134
the result of the below function is 04/12/2012 9:21:34 AM

Public Function convertdate(byval strdate as string) as date
    Dim stryear as string, strmonth as string, strday as string, strhour as string, strmin as string, strsec as string
    stryear = left(strdate,4)
    strmonth = mid(strdate,5,2)
    strday = mid(strdate,7,2)
    strhour = mid(strdate,9,2)
    strmin = mid(strdate,11,2)
    strsec = mid(strdate,13,2)
   converdate = CVDATE(strmonth & "/" & strday & "/" & stryear & " " & strhour & ":" & strmin & ":" & strsec)
End Function

[RayMilhon]

The typo I had where I thought the CVDate wasn't the correct function is I'd left out the space between stryear and strhour so the string it was trying to convert was

04/12/201209:21:34 which gave a type mismatch I mistakenly thought it was because the time was included. after posting here I noticed there was no space between the 2012 and 09 so I made sure the space was included and viola it worked.

[pbaldy]

Wasn't familiar with CVDate, but it appears to be functionally equivalent to CDate:

?cdate("04/12/2012 09:21:34")
4/12/2012 9:21:34 AM

[RayMilhon]

Guess I was doing something wrong in CDate as well every time I tried it I only got the date not the time

[pbaldy]

I was actually surprised it handled the time, which is why I suggested the other functions earlier. Learn something new every day.