Looking for a "String To Date" function...

[Lynn Pennington]

Hello.

I have a field in a table with the string
format "YYYYMMDD".
What would be the best way to convert that to a date
format?

dDateField = CDate(Mid(sDateField,5,2) & "/" & Right
(sDateField,2) & "/" & Left(sDateField,4))

Thanks,
Lynn Pennington

 

[Marshall Barton]

I have a field in a table with the string
format "YYYYMMDD".
What would be the best way to convert that to a date
format?

dDateField = CDate(Mid(sDateField,5,2) & "/" & Right
(sDateField,2) & "/" & Left(sDateField,4))

That should work ok. Another way is:

DateSerial(Left(sDateField,4), Mid(sDateField,5,2),
Right(sDateField,2))

"Best way" is often a matter of opinion.

 

[Lynn Pennington]

LOL.
Thanks - I am not that familiar with all the built-in
functions and just wanted to make sure.
Thanks again.
Lynn.

 

[Douglas J. Steele]

That should work ok. Another way is:

DateSerial(Left(sDateField,4), Mid(sDateField,5,2),
Right(sDateField,2))

"Best way" is often a matter of opinion.

Actually, in this case I think DateSerial is superior to CDate.

DateSerial will work regardless of the user's Regional Settings. CDate
respects the workstation's Short Date format, so will return the incorrect
date if the user has chosen dd/mm/yyyy as their format.

 

[Marshall Barton]

Actually, in this case I think DateSerial is superior to CDate.

DateSerial will work regardless of the user's Regional Settings. CDate
respects the workstation's Short Date format, so will return the incorrect
date if the user has chosen dd/mm/yyyy as their format.

Good point Doug, I forgot about that.

I will no longer abstain and now vote for DateSerial
as "The Best Way" ;-)