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
  • PowerCenter 10.5
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

Back Next

TO_DATE

TO_DATE

Converts a character string to a Date/Time datatype. You use the TO_DATE format strings to specify the format of the source strings.
The output port must be Date/Time for TO_DATE expressions.
If you are converting two-digit years with TO_DATE, use either the RR or YY format string. Do not use the YYYY format string.

Syntax

TO_DATE( 
string
 [, 
format
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
string
Required
Must be a string datatype. Passes the values that you want to convert to dates. 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 1998 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

Date.
TO_DATE always returns a date and time. If you pass a string that does not have a time value, the date returned always includes the time 00:00:00.000000000. You can map the results of this function to any target column with a datetime datatype. If the target column precision is less than nanoseconds, the
PowerCenter Integration Service
 truncates the datetime value to match the precision of the target column when it writes datetime values to the target.
NULL if you pass a null value to this function.
The format of the TO_DATE string must match the format string including any date separators. If it does not, the
PowerCenter Integration Service
 might return inaccurate values or skip the record.

Examples

The following expression returns date values for the strings in the DATE_PROMISED port. TO_DATE always returns a date and time. If you pass a string that does not have a time value, the date returned always includes the time 00:00:00.000000000. If you run a session in the twentieth century, the century will be 19. In this example, the current year on the node running the
PowerCenter Integration Service
 is 1998. The datetime format for the target column is MON DD YY HH24:MI SS, so the
PowerCenter Integration Service
 truncates the datetime value to seconds when it writes to the target: 
TO_DATE( DATE_PROMISED, 'MM/DD/YY' )
DATE_PROMISED
RETURN VALUE
 
'01/22/98'
Jan 22 1998 00:00:00
'05/03/98'
May 3 1998 00:00:00
'11/10/98'
Nov 10 1998 00:00:00
'10/19/98'
Oct 19 1998 00:00:00
NULL
NULL
The following expression returns date and time values for the strings in the DATE_PROMISED port. If you pass a string that does not have a time value, the
PowerCenter Integration Service
 returns an error. If you run a session in the twentieth century, the century will be 19. The current year on the node running the
PowerCenter Integration Service
 is 1998: 
TO_DATE( DATE_PROMISED, 'MON DD YYYY HH12:MI:SSAM' )
DATE_PROMISED
RETURN VALUE
 
'Jan 22 1998 02:14:56PM'
Jan 22 1998 02:14:56PM
'Mar 15 1998 11:11:11AM'
Mar 15 1998 11:11:11AM
'Jun 18 1998 10:10:10PM'
Jun 18 1998 10:10:10PM
'October 19 1998'
Error. Integration Service skips this row.
 
NULL
NULL
The following expression converts strings in the SHIP_DATE_MJD_STRING port to date values: 
TO_DATE (SHIP_DATE_MJD_STR, 'J')
SHIP_DATE_MJD_STR
RETURN_VALUE
 
'2451544'
Dec 31 1999 00:00:00.000000000
'2415021'
Jan 1 1900 00:00:00.000000000
Because the J format string does not include the time portion of a date, the return values have the time set to 00:00:00.000000000.
The following expression converts a string to a four-digit year format. The current year is 1998: 
TO_DATE( DATE_STR, 'MM/DD/RR')
DATE_STR
RETURN VALUE
 
'04/01/98'
04/01/1998 00:00:00.000000000
'08/17/05'
08/17/2005 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998: 
TO_DATE( DATE_STR, 'MM/DD/YY')
DATE_STR
RETURN VALUE
 
'04/01/98'
04/01/1998 00:00:00.000000000
'08/17/05'
08/17/1905 00:00:00.000000000
For the second row, RR returns the year 2005 and YY returns the year 1905.
The following expression converts a string to a four-digit year format. The current year is 1998: 
TO_DATE( DATE_STR, 'MM/DD/Y')
DATE_STR
RETURN VALUE
 
'04/01/8'
04/01/1998 00:00:00.000000000
'08/17/5'
08/17/1995 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998: 
TO_DATE( DATE_STR, 'MM/DD/YYY')
DATE_STR
RETURN VALUE
 
'04/01/998'
04/01/1998 00:00:00.000000000
'08/17/995'
08/17/1995 00:00:00.000000000
The following expression converts strings that includes the seconds since midnight to date values: 
TO_DATE( DATE_STR, 'MM/DD/YYYY SSSSS')
DATE_STR
RETURN_VALUE
 
'12/31/1999 3783'
12/31/1999 01:02:03
'09/15/1996 86399'
09/15/1996 23:59:59
If the target accepts different date formats, use TO_DATE and IS_DATE with the DECODE function to test for acceptable formats. For example: 
DECODE( TRUE,

 --test first format
   IS_DATE( CLOSE_DATE,'MM/DD/YYYY HH24:MI:SS' ),

 --if true, convert to date
      TO_DATE( CLOSE_DATE,'MM/DD/YYYY HH24:MI:SS' ),

 --test second format; if true, convert to date
   IS_DATE( CLOSE_DATE,'MM/DD/YYYY'), TO_DATE( CLOSE_DATE,'MM/DD/YYYY' ),

 --test third format; if true, convert to date
   IS_DATE( CLOSE_DATE,'MON DD YYYY'), TO_DATE( CLOSE_DATE,'MON DD YYYY'),

 --if none of the above
   ERROR( 'NOT A VALID DATE') )
You can combine TO_CHAR and TO_DATE to convert a numeric value for a month into the text value for a month using a function such as: 
TO_CHAR( TO_DATE( numeric_month, 'MM' ), 'MONTH' )
0 COMMENTS
We’d like to hear from you!