Converting a numeric field into a date

jtequia
jtequia used Ask the Experts™
on
I want to take a numeric 8 digit field that holds a number representing a date mmddyyyy, but where the month ranges from 1-9 the number is stored as mddyyyy.  I want to convert this into a date field in a query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
If the data is stored in a string then use this:

? DateSerial(Right(Format("9012011","00000000"),4), Left(Format("9012011","00000000"),2) ,Mid(Format("9012011","00000000"),3,2))
9/1/2011 

Open in new window


if numeric:

? DateSerial(Right(Format(9012011,"00000000"),4), Left(Format(9012011,"00000000"),2) ,Mid(Format(9012011,"00000000"),3,2))
9/1/2011 

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can do a direct numeric conversion:


MyDate: DateSerial([YourNumField] Mod 10000,[YourNumField]\1000000,[YourNumField]\10000 Mod 100)

/gustav
Top Expert 2011

Commented:
gustav,

Great use of Mod

? DateSerial(9012011 Mod 10000,9012011\1000000,9012011\10000 Mod 100)
9/1/2011 

? DateSerial(CDbl("9012011") Mod 10000, CDbl("9012011")\1000000,CDbl("9012011")\10000 Mod 100)
9/1/2011 


? DateSerial(10012011 Mod 10000,10012011\1000000,10012011\10000 Mod 100)
10/1/2011 

? DateSerial(CDbl("10012011") Mod 10000, CDbl("10012011")\1000000,CDbl("10012011")\10000 Mod 100)
10/1/2011 

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, Mod is very useful in some cases.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial