Date Conversion from the Mainframe as YYYYMMDD

[jjc3397]

I have an Access Database with a Date Field called Leave Progression Date. This date is coming in as yyyymmdd. I need a quick and dirty way to change all of the dates in 850 records at one time to mmddyyyy or even a short date such as mmddyy would be fine.

I know an update query will do this, but I do not know what coding would do this for me quickly.

 

[WildHare]

Here's one quick way, using the CDATE conversion function. First step is splitting out the parts of your date:

MyNewDate = CDate(Mid(MFDate,5,2) & "/" & Right(MFDate,2) & "/" & Left(MFDate,1,4))

The key here is arranging the parts of the date into the MM/DD/YYYY order that Access expects dates to be in - then the CDATE simply converts that to an actual date value.

If you have internationalized your date format, e.g. "DDMMYY", just alter the formula to match.

There's probably a more elegant way of doing it, but this works as well.




"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman