DATE2 (standardization function)

Using the DATE2 standardization function along with the DATE2 comparison function enables comparison of incomplete or partially valid dates.

Because incomplete or partially valid dates are removed when stored in the mpi_memdate segment, you might want to store these dates in mpi_memattr.

The DATE2 function has a length limitation of 19 digits. If the value of the length of the DATE2 field is over 19 digits, the operational server truncates the input value to the first 19 digits of the value.

Output Type
Numeric (date)
Fldargs
dateval or attrval
Dvdarg
Min year, Max year
MinFldArgs, MaxFldArgs
1,1
Number of standard roles
1
  1. This function outputs an eight-digit string. If year, month or day is invalid, those portions of the date are replaced by a string of 0's (zeros) and the comparison function ignores the 0 string.
  2. The function is configurable with a minimum and maximum year. When the date falls outside of those ranges, the entire date is treated as an anonymous value.
  3. After you apply the year range filter, DATE2 checks the date against a configurable date standardization table.
  4. The entries for this table are stored in mpi_strequi table that has length 8 and length 4 entries. The length 8 entries are MMDDYYYY and length 4 entries are YYYY. Length 4 entries map the entire input string to a standard string. The length 8 string maps only the month and day portions of the string. This means that strval2 is replaced by strval1.
Table 1. DATE2 example
STRVAL2 STRVAL1 LENGTH FORMAT
0101 0000 4 = YYYY
02292007 01032007 8 = MMDDYYYY
In the example:
  1. The first entry specifies that any month and day of 0101 should be mapped to 0000 or marked as invalid. Since the year is left as is, for any date with a month and day of January 1, only the year is used.
  2. The second entry specifies that the date 02292007 should be changed to 01032007.
  3. After you apply the date standardization table, the month and date is checked. If the month is between 1 and 12, the month is left as is. Otherwise, the month is set at 00.
  4. If the output month is not valid and the day is between 1 and 31, the day is left as is. Otherwise, the day is set to 00.
  5. If the month and year are valid, the month and year are used to determine if the day is valid. If the day is valid, it is left as is. Otherwise, the day is set to 00.
  6. If the month is valid, but the year is not, then determination is made on whether the day is valid (leap year is assumed). If the day is valid, it is left as is. Otherwise, the day is set to 00.
  7. After you complete the day analysis, if one or both of the month and day entries is invalid:
    1. Return to the original month and day and transpose the month and day. Analysis is repeated.
    2. If analysis yields a valid month and day, the transposed date is used in the output with a prefix of ‘T'.