Access data conversion issue

Question

I'm using Access 2003. Have a table with some date values in a text data column like this;

May-97
Jun-99
Jun-00
Sep-02
Jan-04

I need to convert them to proper date format and into another Date/time column, So create a new Date/Time columns and just updated the values from the Text column into this new column. At first it looked fine, except for years after the year 2000. The new columns converted the dates as follows;

May-97 > 01/05/1997
Jun-99 > 01/06/1999
Jun-00 > 01/06/2000
Sep-02 > 01/09/2010
Jan-04 > 01/01/2010

As you can see any data with year after 2000 get converted to 2010. The same thing happens if I query the data using FORMAT(dateString, "dd/mm/yyyy").

Any ideas why this is so? Do I have to split the month and year and combine them again?

Thanks

Answer 1

Access seems to be get conduced between MM-YYYY format and MM-DD format. Don't know why it is doing it for dates after the year 2000, but solved it by converting the original string date to full date (01-May-01). Now Access converts the year into 2001 instead of 2010.

Answer 2

The date is ambiguous, so it is seeing 02 as the day number. Depending on your locale, something like this may suit:

cdate("01-" & Field)

However, it may be best to convert to four digit year, month, day format, which is always unambiguous.

Answer 3

If you don't supply a year and the two sets of digits entered into a date field could be a day and month then Access assumes the current year. So your first three dates definitely have a year in them. But the last two don't.

Note that this isn't Access but actually the operating system doing the work. You get the same results in Excel. I had an interesting conversattion with some Microsoft employees on this issue and it's actually OLEAUT32.DLL.

Answer 4

Access/Jet/ACE (and many other Windows components) use a window for interpreting 2-digit years. For 00 to 29, it's assumed to be 2000-2029, and for 30-99, 1930-1999. This was put in place to address Y2K compatibility issues sometime in the 1997-98 time frame.

I do not allow 2-digit year input anywhere in any of my apps. Because of that, I don't have to have any code to interpret what is intended by the user (which could conceivably make mistakes).

This also points up the issue of the independence of display format and data storage with Jet/ACE date values. The storage is as a double, with the integer part indicating the day since 12/30/1899 and the decimal part the time portion within the day. Any date you enter is going to be stored as only one number.

If you input an incomplete date (i.e., with no century explicitly indicated for the year), your application has to make an assumption as to what the user intends. The 2029 window is one solution to the 2-digit year problem, but in my opinion, it's entirely inappropriate to depend on it because the user can change it in their Control Panel Regional Settings. I don't write any complicated code to verify dates, I just require 4-digit year entry and avoid the problem entirely. I have been doing this since c. 1998 as a matter of course, and everybody is completely accustomed to it. A few users squawked back then, and I had the "it's because of Y2K" as the excuse that shut them down. Once they got used it, it became a non-issue.