User Guide : Scripting : Using Scripts to Perform Specific Tasks : Using Masks to Convert Two-Digit Dates
 
Share this page                  
Using Masks to Convert Two-Digit Dates
This section 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.
Mask Type for Two-Digit Dates, "YYdddd"
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.
Tip:  Use the DateConvert mask as the source format, not the target format.
Syntax Notes
dd dd
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.
Example 1
Date mask "YY1900/mm/dd"
'Specifies that all of the dates are in the range of 1900/01/01 to 1999/12/31. The base century is 1900 and all two-digit years are mapped into the base century.
Date mask "YY1950/mm/dd"
'Indicates that all two-digit years starting with 50 are mapped into the 1900s. The two-digit years less than 50 are mapped into the next century (2000s).
Date mask "YY1970/mm/dd" 'Similar mapping as above, but the split starts for years less than 70.
Note:  If no numeric value follows the "YY", then the base century is 0 and there is no split. If the numeric value is greater than 9900, then the base century is also treated as 0. All other values between 0 and 9900 work as described above.
Example 2
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.
If Right(FieldAt("/SOURCE/R1/Field1"), 2) >= 50 Then
  Left(FieldAt("/SOURCE/R1/Field1"), 6) & "19" & Right(FieldAt("/SOURCE/R1/Field1"), 2)
Else
  Left(FieldAt("/SOURCE/R1/Field1"), 6) & "20" & Right(FieldAt("/SOURCE/R1/Field1"), 2)
End If
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.
Avoiding Data Truncation
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.
Example 1
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:
If Right(FieldAt("/SOURCE/R1/Field1") , 2) >= 50 Then
  DateValMask(Left(FieldAt("/SOURCE/R1/Field1") , 6) & "19" & Right(FieldAt("/SOURCE/R1/Field1") , 2), "mm/dd/yyyy")
Else
  DateValMask(Left(FieldAt("/SOURCE/R1/Field1") , 6) & "20" & Right(FieldAt("/SOURCE/R1/Field1") , 2), "mm/dd/yyyy")
End If
Result: Access DateTime/ 11/22/1998 and 12/22/2045
Example 2
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:
If Left(FieldAt("/SOURCE/R1/Field3") , 2) >= 40 Then
  Left(DateConvert("yyddd", "mm/dd/yy", FieldAt("/SOURCE/R1/Field3") ) , 6) & "19" & Right(DateConvert("yyddd", "mm/dd/yy", FieldAt("/SOURCE/R1/Field3") ) , 2)
Else
  Left(DateConvert("yyddd", "mm/dd/yy", FieldAt("/SOURCE/R1/Field3") ) , 6) & "20" & Right(DateConvert("yyddd", "mm/dd/yy", FieldAt("/SOURCE/R1/Field3") ) , 2)
End If
Result: Text/ 12/26/1999 and 10/03/2036
Example 3
Source type: Text
Data: 980409 and 640722
Converting unformatted text date with year first to an access date with 70 as the cut-off:
If Left(FieldAt("/SOURCE/R1/Field5") , 2) >= 70 Then
  DateValMask("19" & Left(FieldAt("/SOURCE/R1/Field5") , 2) & Right(FieldAt("/SOURCE/R1/Field5") , 4), "yyyymmdd")
Else
  DateValMask("20" & Left(FieldAt("/SOURCE/R1/Field5") , 2) & Right(FieldAt("/SOURCE/R1/Field5") , 4), "yyyymmdd")

End If
Result: Access Date Time/ 04/09/1998 and 07/22/2064
See Also
DateConvert Function
DateValue Function
DateValMask Function