This topic relates to situations where the source file contains date values with a two-digit year. Two-digit years are frequently found in legacy systems stored in ASCII, COBOL, or other binary data formats. Any time you need to convert two-digit year, use a date mask or an expression similar to the ones below.

By default, the three most commonly used date conversion functions, DateConvert, DateValue, and DateValMask, use the workstation system date to set the century when converting two-digit to four-digit dates. This means that if the system date is 1999, these functions adds a 19 unless you specify otherwise in an expression like the one below. If the system date is 2000, these functions add a 20 to the two-digit input date unless you specify otherwise in an expression like the one below. You can also use the new mask type for two-digit dates, as described below.

Regarding Year 2000 date conversion, no assumptions are made as to what you want to do with dates. Mapping does not impose any standard for where to split the dates that may or may not work for your data needs. Instead, you can use expressions to manipulate your data as you see fit.

Use the format YYdddd in functions that employ a mask, such as DateValMask or DateConvert. For example, use it in Y2K conversions by specifying the century split to be automatically applied.

Is a four digit (or less) value used to specify the base century and the century split. The right-most two digits are used to indicate the split and the left-most digits indicate the base century.

Date mask "YY1970/mm/dd" 'Similar mapping as above, but the split starts for years less than 70.

Below is an example of an expression that manipulates dates using the "50 / 51" standard. The date mask in the source file is "mm/dd/yy" or "mm-dd-yy", and both the source and target data types are Text.

This expression checks for a year value greater than or equal to 50, concatenates "19" if the year is more than 50, or concatenates 20 if less than 50. It is used to transform two-digit text dates into four-digit text dates. The 50 can be replaced with whatever number your company uses as the cut-off for Year 2000 data conversions, or whatever number seems appropriate for the data in that particular field. An expiration date field, for example, would be likely to need a different cut-off number than a birthdate field.

If converting to a text field, make sure the size of the target field is large enough to accommodate the extra two characters. If the target is a date field, you must use the DateValue or the DateValMask function. See the following examples for how the source type data can be transformed into the target type result.

Source type: Text

Source data: 11/22/98 and 12/22/45

Converting a standard formatted text date to an Access DateTime field with 50 as the cut-off:

Result: Access DateTime/ 11/22/1998 and 12/22/2045

Source data: 5-digit Julian date/ 99360 and 36277

Converting a Julian date with two digit year and three-digit day of the year with 40 as the cut-off:

Result: Text/ 12/26/1999 and 10/03/2036

Source type: Text

Data: 980409 and 640722

Converting unformatted text date with year first to an access date with 70 as the cut-off:

Result: Access Date Time/ 04/09/1998 and 07/22/2064