Convert number to date

[FergusonH]

Good Morning! I've searched the posts and found several things that hint
around how to do this, but haven't quite found how to convert a number field
to a date.

My number field is formated as 033109 where I'd like to have mmddyy, so
March 31, 2009.

How do I convert in query syntax? DateSerial is getting me close but I must
have it a little wrong.

thanks!

 

[John Spencer MVP]

Is that really a number or is it a text field that contains number characters?
If it is a text field with number characters and there are always 6
characters then you can use the following assuming your field name is X

IIF(IsDate(Format(X,"@@/@@/@@")),CDate(Format(X,"@@/@@/@@")),Null)

Or you can use the following to do the conversion
DateSerial(Right(X,2),Left(X,2),Mid(X,3,2))
That will work as long as you always have 6 characters and NEVER have a null
value.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

Well, numbers stored in tables don't have leading zeros, so is your field a
string with numeric values, or is it a number? I'm also unsure what format
you want the output in, is it mmm dd, yyyy?

Lets assume your field is actually a string, and will always contain 6 digits
(mmddyy). Then to get March 31, 2009, you would need something like:

format(dateserial(val(2000 + mid(strDate,5)), val(left(strDate, 2)), val(mid
(strDate, 3,2))), "mmm dd, yyyy")

If you actually have a number, then the parsing is a little different

?format(dateserial(2000 + (intDate mod 100), intDate\10000, (intDate\100) mod
100), "mmm dd, yyyy")

HTH
Dale

 

[FergusonH]

Thank you John! The Serial date worked! Thank you thank you!