TO_DATE
Converts a character string to a date datatype in the same format as the character string. You use the TO_DATE format strings to specify the format of the source strings.
The target column 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] )
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 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 default date of MM/DD/YYYY HH24:MI:SS. |
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. You can map the results of this function to any target column with a date datatype.
NULL if you pass a null value to this function.
Warning: The format of the TO_DATE string must match the format string including any date separators. If it does not, Informatica Cloud might return inaccurate values or skip the row.
Example
The following expression returns date values for the strings in the DATE_PROMISED column. 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. If you run a session in the twentieth century, the century will be 19. The current year on the machine running Informatica Cloud is 1998:
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 column. If you pass a string that does not have a time value, Informatica Cloud writes the row into the error rows file. If you run a session in the twentieth century, the century will be 19. The current year on the machine running Informatica Cloud 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' | None. Informatica Cloud writes the row into the error rows file. |
NULL | NULL |
The following expression converts strings in the SHIP_DATE_MJD_STRING column to date values in the default date format:
TO_DATE (SHIP_DATE_MJD_STR, 'J')
SHIP_DATE_MJD_STR | RETURN_VALUE |
|---|
'2451544' | Dec 31 1999 00:00:00 |
'2415021' | Jan 1 1900 00:00:00 |
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.
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 |
'08/17/05' | 08/17/2005 00:00:00 |
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 |
'08/17/05' | 08/17/1905 00:00:00 |
Note: 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 |
'08/17/5' | 08/17/1995 00:00:00 |
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 |
'08/17/995' | 08/17/1995 00:00:00 |
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' )