• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

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
0
YZlat
Asked:
YZlat
  • 4
  • 2
1 Solution
 
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
0
 
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

0
 
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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.
0
 
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now