A valid date is any string in the default date format of MM/DD/YYYY HH24:MI:SS. If the strings you want to test are not in the default date format, use the TO_DATE format strings to specify the date format. If the strings passed to IS_DATE do not match the format string specified, the function returns FALSE (0). If the strings match the format string, the function returns TRUE (1).

IS_DATE evaluates strings and returns an integer value.

The target column for an IS_DATE expression must be String or Numeric datatype.

You might use IS_DATE to test or filter data in a flat file before writing it to a target.

Use the RR format string with IS_DATE instead of the YY format string. In most cases, the two format strings return the same values, but there are some unique cases where YY returns incorrect results. For example, the expression IS_DATE(‘02/29/00’, ‘YY’) is internally computed as IS_DATE(02/29/1900 00:00:00), which returns false. However,

Data Integration

computes the expression IS_DATE(‘02/29/00’, ‘RR’) as IS_DATE(02/29/2000 00:00:00), which returns TRUE. In the first case, year 1900 is not a leap year, so there is no February 29th.

TRUE (1) if the row is a valid date.

FALSE (0) if the row is not a valid date.

NULL if a value in the expression is NULL or if the format string is NULL.

The following expression checks the INVOICE_DATE column for valid dates:

This expression returns data similar to the following:

The following IS_DATE expression specifies a format string of ‘YYYY/MM/DD’:

If the string value does not match this format, IS_DATE returns FALSE:

The following example shows how you use IS_DATE to test data before using TO_DATE to convert the strings to dates. This expression checks the values in the INVOICE_DATE column and converts each valid date to a date value. If the value is not a valid date,

Data Integration

returns ERROR and skips the row.

This example returns a Date/Time value. Therefore, the target column for the expression needs to be Date/Time: