469,330 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Convert string to Date

393 256MB
I'm getting a tab delimited file that I need to import into an access DB. The date field comes over as an 8 character string in the yyyymmdd format.

Does anyone know an easy way to convert this to a date or a pre-existing function?
May 5 '11 #1
5 21752
2,321 Expert Mod 2GB
Try the built in function cdate. It should meet your needs
May 5 '11 #2
393 256MB
You might think so, but it doesn't convert this to a date. As a string, I get 'expected expression'. As a number I get 'out of range'.

I expected the number to fail, but I thought the string would work.
May 5 '11 #3
12,516 Expert Mod 8TB
That's because the yyyymmdd format isn't a recognized format by Access. Strings without delimiters aren't recognized as dates.
CDate(Mid(strDate, 5,2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4))
May 5 '11 #4
393 256MB
Yeah, I had a feeling this was the route I was going. I was just figuring there was a predefined function. Oh well, I guess I'll just write it. :)
May 5 '11 #5
32,181 Expert Mod 16PB
You can happily use this that I produced a while back :

Expand|Select|Wrap|Line Numbers
  1. 'YMDToDate takes varDate in YYYYMM or YYYYMMDD format and returns it as a date.
  2. '28/01/2004 Return Null if not valid format.
  3. Public Function YMDToDate(varDate As Variant) As Variant
  4.     Dim strDMY As String
  6.     strDMY = Mid(varDate, 5, 2) & "/" & Left(varDate, 4)
  7.     If Len(varDate) = 8 Then strDMY = Mid(varDate, 7, 2) & "/" & strDMY
  8.     YMDToDate = Null
  9.     If IsDate(strDMY) Then YMDToDate = CDate(strDMY)
  10. End Function
May 10 '11 #6

Post your reply

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

Similar topics

4 posts views Thread by Richard Hollenbeck | last post: by
2 posts views Thread by Abhishek Srivastava | last post: by
1 post views Thread by Sam | last post: by
5 posts views Thread by XML newbie: Urgent pls help! | last post: by
5 posts views Thread by satiss7pwr | last post: by
3 posts views Thread by tanishka singh | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.