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.

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

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

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

Brian

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.

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.

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

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.