Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

IS_DATE

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.
IS_DATE uses the same format strings as TO_DATE.

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 mapping configuration.

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.
The format of the IS_DATE string must match the format string, including any date separators. If it does not, the
Data Integration Service
might return inaccurate values or skip the record.

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'

0 COMMENTS

We’d like to hear from you!