Convert text date to date/time

  • Thread starter Thread starter Nico
  • Start date Start date
N

Nico

The date and time is stored as text, for example, as "Thursday, May 22, 2008
4:54 PM", but I need to query from it so I need it in a recognizable
date/time format.

Any advice on how to convert it?

Thanks!
 
CDate is the function that you want. It will convert text string that can be
evaluated as a valid date/time into an actual date/time.

The problem is that CDate doesn't play well with non-valid dates. Therefore
I usually check with IsDate first and handle bogus dates. Something like
below will convert valid dates and put in a bogus 1/1/1950 for non-valid date
strings or nulls.

SELECT IIf(IsDate([date])=True,CDate([date]),#1/1/1950#) AS NotDate
FROM tblDatesText;

However your dates have a problem:
Debug.Print IsDate("Thursday, May 22, 2008 4:54 PM") = False

Whereas
Debug.Print IsDate("May 22, 2008 4:54 PM") = True

So you might need to strip out the day of the week. Assuming that all of the
records have one, something like below would work:

Mid("Thursday, May 22, 2008 4:54 PM",Instr("Thursday, May 22, 2008 4:54 PM",
" ")+1)

Therefore it might take something like:

SELECT IIf(IsDate(Mid([date],Instr([date], " ")+1))=True,
CDate(Mid([date],Instr([date], " ")+1)),
#1/1/1950#) AS NotDate
FROM tblDatesText;
 
If you can remove the name of the day, use CDate:


? CDate( "May 22, 2008 4:54 PM")
2008.05.22 16:54:00


(note that my format is ISO 24h, but any US based OS should be able to
recognize your American date, 12h, AM/PM format)


To eliminate the day name, either do it at the source, probably the faster
way, either spot the first coma:

InStr( yourString, ",")


and use

CDate (MID( yourString, 1+ InStr(yourString, "," ) ))


Hoping it may help,
Vanderghast, Access MVP
 
This worked perfectly, thanks so much for your help!

Jerry Whittle said:
CDate is the function that you want. It will convert text string that can be
evaluated as a valid date/time into an actual date/time.

The problem is that CDate doesn't play well with non-valid dates. Therefore
I usually check with IsDate first and handle bogus dates. Something like
below will convert valid dates and put in a bogus 1/1/1950 for non-valid date
strings or nulls.

SELECT IIf(IsDate([date])=True,CDate([date]),#1/1/1950#) AS NotDate
FROM tblDatesText;

However your dates have a problem:
Debug.Print IsDate("Thursday, May 22, 2008 4:54 PM") = False

Whereas
Debug.Print IsDate("May 22, 2008 4:54 PM") = True

So you might need to strip out the day of the week. Assuming that all of the
records have one, something like below would work:

Mid("Thursday, May 22, 2008 4:54 PM",Instr("Thursday, May 22, 2008 4:54 PM",
" ")+1)

Therefore it might take something like:

SELECT IIf(IsDate(Mid([date],Instr([date], " ")+1))=True,
CDate(Mid([date],Instr([date], " ")+1)),
#1/1/1950#) AS NotDate
FROM tblDatesText;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Nico said:
The date and time is stored as text, for example, as "Thursday, May 22, 2008
4:54 PM", but I need to query from it so I need it in a recognizable
date/time format.

Any advice on how to convert it?

Thanks!
Click to expand...
Click to expand...
 
Back
Top