Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Informatica Data Quality 10.5.1
  • All Products

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

Back Next

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!