?
Solved

convert string to DateTime

Posted on 2006-04-07
6
Medium Priority
?
845 Views
Last Modified: 2008-02-01
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
Comment
Question by:YZlat
  • 4
  • 2
6 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 400 total points
ID: 16402549
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
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16402999
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16403181
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16403193
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
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16404254
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16404376
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question