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
LVL 35
YZlatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
DateSerial(Left(DueDate,4),Mid(DueDate,5,2),Mid(7,2)) to get the date
TimerSerial(Mid(9,2),Mid(11,2),Mid(13)) as the time

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick_RickardsCommented:
LPurvis is on the right track but there are a couple syntax errors that I hope to have corrected below...

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

Leigh PurvisDatabase DeveloperCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Leigh PurvisDatabase DeveloperCommented:
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.
Rick_RickardsCommented:
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
Leigh PurvisDatabase DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.