Tell me Y2K you cry
By Richard Mullins NICAR/Missouri School of Journalism
Access and FoxPro don't have a year 2000 problem - they can handle dates from 100 A.D. up to 9999 A.D. That should handle the worst jail terms and back-dated purchase orders.
So the real Year 2K problem is yours. You need to understand a little of how Access and FoxPro store dates and know the assumptions that Access and FoxPro use for implying the century when you only enter or import two digits for the calendar year.
FoxPro and Access do not store any dates with only two digits for the year, leaving the century ambiguous or unknown. Both of them, however, give you various ways to control the display format of dates, including whether the year has two or four digits.
Let's deal with FoxPro first. You can toggle the display of the century with the SET CENTURY command. You can test this feature in the command window to see how it works. The lines in bold are the responses to the print-to-screen command. If computer clock has the current date as July 1, 1998, then:
set century ON ? date ( ) 7/1/1998 * back to the default set century OFF ? date ( ) 7/1/98
When converting dates, FoxPro assumes all two-digit years to be 20th century, regardless of the current setting of CENTURY. This default can be overridden.
It is a data-processing standard at NICAR to convert all character dates to the FoxPro date datatype. We also preserve the original character date column. That way, there is no loss of original data and conversions can be checked and re-done. For any character representations of a date that do not represent a valid calendar date, like 19950335, FoxPro deals with this by converting it to a null or empty date. You can do a query for empty dates to see what was in the character original. Here is a query using the FEC data as an example. The column chardate is the character original; cont_date contains the date datatype.
SELECT chardate, cont_date; FROM Indc079 ; WHERE empty (cont_date)
The empty ( ) function in the criteria is the same as:
WHERE cont_date = }
Unlike FoxPro, Access makes a conditional assumption about the implied century for a two-digit year. The cutoff year number is 30. Two-digit years of 30 and higher are assumed to be 20th century. Two-digit years from zero to 29 are assumed to be 21st century.
Here is what the test of this looks like in the Access Debug window. (Open a new or existing module, then choose View/Debug Window.)
? cvdate ("07/01/98") 7/1/98 ? cvdate "07/01/28") 7/1/2028
Here is the issue you'll have to consider when looking at new data sets. Do they have date information with two-digit years? If so, can you do integrity and reality checks to determine that some dates should be 21st Century and not 20th. Some obvious examples: an inmate gets out in the year 2015, not 1915; a building was built in 1920, not 2020. If so, you can make the appropriate conversion.
NICAR has been evaluating the data we get for the data library and for custom data jobs with this issue in mind, but so far the century issue has not come up.
Richard Mullins can be reached at (573) 882-2737 or by e-mail at richard@nicar.org