convert string to DateTime
I have an Access table with a field DueDate as a text field. This field contains a date and time in a string format: yyyyMMddhhmmss. How can I convert it to the type DateTime from a select query?
For example I have a DueDate field =200604010925242
I run a select query:
Select DueDate from Table1
and I want to retrieve 04/01/2006 09:25:42 instead of 200604010925242
For example I have a DueDate field =200604010925242
I run a select query:
Select DueDate from Table1
and I want to retrieve 04/01/2006 09:25:42 instead of 200604010925242
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh - the intention was for that to be
Mid(13,2)
(I was dashing out ;-)
Out of interest - your string ended with "...0925242"
But that doesn't seem to coincide with "09:25:42".
There's an extra 2 in there.
Mid(13,2)
(I was dashing out ;-)
Out of interest - your string ended with "...0925242"
But that doesn't seem to coincide with "09:25:42".
There's an extra 2 in there.
Sorry - I was dashing - but did mean it to be just Mid(13) - I should have more faith in myself - even when hurrying lol
The problem you've found was due to the issue I raised - with that extra 2.
The problem you've found was due to the issue I raised - with that extra 2.
Not to worry, I've dropped my own errors in an effort to be expediant. Either way it would seem they liked your answer. The most noticiable thing about your post that got my attention was the use of TimerSerial instead of TimeSerial. For a moment you had me thinking there was a function I'd never heard of before. <wink>
Rick
Rick
Well - air code can be a curious thing :-)
I usually like to offer aircode that will run first time - but those moments when you've started a question - and the shout comes from the front door "Come on!!" - you just bash it into the window - hit enter and you're off.
I usually like to offer aircode that will run first time - but those moments when you've started a question - and the shout comes from the front door "Come on!!" - you just bash it into the window - hit enter and you're off.
DateSerial(Left(DueDate, 4), Mid(DueDate, 5, 2), Mid(DueDate, 7, 2)) + TimeSerial(Mid(DueDate, 9, 2), Mid(DueDate, 11, 2), Mid(DueDate, 14))
One point of curiosity, the 13the character doesn't seem to have any meaning in your example. Was this intentional or have we missed something?
Rick