IS_DATE
Returns whether a string value is a valid date. A valid date is any string in the date portion of the date time format specified in the session. If the string you want to test is not in this date format, use the TO_DATE format string 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 output port 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, the Data Integration Service 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.
Syntax
IS_DATE( value [,format] )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
value | Required | Must be a string datatype. Passes the rows you want to evaluate. You can enter any valid transformation 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 date format specified in the session. |
Return Value
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.
Examples
The following expression checks the INVOICE_DATE port 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.7008' | 1 (TRUE) |
'02/31/1998 12:13:55.9204' | 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 port and converts each valid date to a date value. If the value is not a valid date, the Data Integration Service returns ERROR and skips the row.
This example returns a Date/Time value. Therefore, the output port for the expression needs to be Date/Time:
IIF( IS_DATE ( INVOICE_DATE, 'YYYY/MM/DD' ), TO_DATE( INVOICE_DATE ), ERROR('Not a valid date' ) )
INVOICE_DATE | RETURN VALUE |
|---|
NULL | NULL |
'180' | 'Not a valid date' |
'04/01/98' | 'Not a valid date' |
'1998/01/12' | 1998/01/12 |
'1998/11/21 00:00:13' | 'Not a valid date' |
'1998/02/31' | 'Not a valid date' |
'John Smith' | 'Not a valid date' |