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.
Note: IS_DATE uses the same format strings as TO_DATE.
Syntax
IS_DATE( value [,format] )
Argument
Required/
Optional
Description
value
Required
Must be a string datatype. Passes the rows you want to evaluate. You can enter any valid expression.
format
Optional
Enter a valid TO_DATE format string. The format string must match the parts of the string argument. For example, if you pass the string 'Mar 15 1997 12:43:10AM', you must use the format string 'MON DD YYYY HH12:MI:SSAM'. If you omit the format string, the string value must be in the default date of MM/DD/YYYY HH24:MI:SS.
NULL if a value in the expression is NULL or if the format string is NULL.
Warning: The format of the IS_DATE string must match the format string, including any date separators. If it does not, Data Integration might return inaccurate values or skip the row.
Example
The following expression checks the INVOICE_DATE column for valid dates:
IS_DATE( INVOICE_DATE )
This expression returns data similar to the following:
INVOICE_DATE
RETURN VALUE
NULL
NULL
'180'
0 (FALSE)
'04/01/98'
0 (FALSE)
'04/01/1998 00:12:15'
1 (TRUE)
'02/31/1998 12:13:55'
0 (FALSE) (February does not have 31 days)
'John Smith'
0 (FALSE)
The following IS_DATE expression specifies a format string of ‘YYYY/MM/DD’:
IS_DATE( INVOICE_DATE, 'YYYY/MM/DD' )
If the string value does not match this format, IS_DATE returns FALSE:
INVOICE_DATE
RETURN VALUE
NULL
NULL
'180'
0 (FALSE)
'04/01/98'
0 (FALSE)
'1998/01/12'
1 (TRUE)
'1998/11/21 00:00:13'
0 (FALSE)
'1998/02/31'
0 (FALSE) (February does not have 31 days)
'John Smith'
0 (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: