Convert number into date please

[joshua362]

Embarassed I can't figure this out.

Have a AS400 dump where the date is a numeric field and is 20081205 for 12/05/2008. How can I convert this to a real date field that I can use and format properly?

Thank you, using Access 2003.

 

[pbaldy]

Look at the DateSerial function, along with the Mid function to pull out the relevant bits.

 

[ezfriend]

Here is something quick and dirty, assuming that the date will always be 8-digit.

Private Function ReturnDate(byval lDateNumber as long) as date
    ReturnDate= cdate(mid(lDateNumber ,2) & "/" & right(lDateNumber ,2) & "/" & left(lDateNumber,4 ))
End Function

 

[Brianwarnock]

The advantage of dateserial is that its format is constant, and it returns the date in your local format.

Brian

 

[LeeInEngland]

Having used the AS400 I an say that it's a tricky one because the day is not always two digits. With my data I also found that if the day was more than 12, the data came out as ddmmyyyy whereas if it was less than 12 it appeared as mmddyyyy.

 

[gemma-the-husky]

that gives you a problem really - thats to do with US dates

ie if you get a

17/03/09 it can ony be march 17th

but if its

05/03/09 it could be march 5th, or May 3rd.

--------
now if you have a mixture of ofrmats you have an issue

you would have to use code to check whether the first two digits were>12 them map it as a UK date, otherwise map it as a US date, then store the converted date in a new field.

 

[Brianwarnock]

He showed a date of 20081205 in which a day of 5 had 2 digits, when will it be only1, will this also apply to month?

Brian

 

[gemma-the-husky]

ah, i was confusing two posts

the OP was talking yyyymmdd

reversing dates like that is a standard technique for sorting them, in character based systems

he won't have any issues slicing a D8 variable with the techniques already outlined

------------
Lee was effectivley saying said he had seen two versions of a date

ddmmyyyy, and mmddyyyy within the same file

which would be a bit harder to deal with.