The TO_DATE function converts a string with the format you specify to a datetime value. TO_DATE is generally used to convert strings from flat files to datetime values. TO_DATE format strings are not internationalized and must be entered in predefined formats as listed in the table below.
Note: TO_DATE and IS_DATE use the same set of format strings.
When you create a TO_DATE expression, use a format string for each part of the date in the source string. The source string format and the format string must match, including any date separators. If any parts do not match, Data Integration does not convert the string and skips the row. If you omit the format string, the source string must be in the default date format MM/DD/YYYY HH24:MI:SS.
IS_DATE tells you if a value is a valid date. A valid date is any string representing a valid date in the default date format of MM/DD/YYYY HH24:MI:SS. If the strings you want to test are not in the default date format, use the format strings listed in the following table to specify the date format. If a string does not match the specified format string or is not a valid date, the function returns FALSE (0). If the string matches the format string and is a valid date, the function returns TRUE (1). IS_DATE format strings are not internationalized and must be entered in predefined formats as listed in the following table.
The following table summarizes the format strings for the functions TO_DATE and IS_DATE:
Format string
Description
AM, a.m., PM, p.m.
Meridian indicator. Use any of these format strings to specify AM and PM hours. AM and PM return the same values as do a.m. and p.m.
DAY
Name of day, including up to nine characters (for example, Wednesday). The DAY format string is not case sensitive.
DD
Day of month (1-31).
DDD
Day of year (001-366, including leap years).
DY
Abbreviated three-character name for a day (for example, Wed). The DY format string is not case sensitive.
HH, HH12
Hour of day (1-12).
HH24
Hour of day (0-23), where 0 is 12AM (midnight).
J
Modified Julian Day. Convert strings in MJD format to date values. It ignores the time component of the source string, assigning all dates the time of 00:00:00.
For example, the expression TO_DATE('2451544', 'J') converts 2451544 to Dec 31 1999 00:00:00.
MI
Minutes (0-59).
MM
Month (1-12).
MONTH
Name of month, including up to nine characters (for example, August). Case does not matter.
MON
Abbreviated three-character name for a month (for example, Aug). Case does not matter.
MS
Milliseconds (0-999).
NS
Nanoseconds. TO_DATE and IS_DATE can support sub-seconds by using the format token 'NS.' The unit is nanosecond. If the sub-second portion is in milliseconds, you can still use it by appending three zeroes as shown in the following examples:
Four-digit year (for example, 1998, 2034). Use when source strings include two-digit years. Use with TO_DATE to convert two-digit years to four-digit years.
- Current Year Between 50 and 99. If the current year is between 50 and 99 (such as 1998) and the year value of the source string is between 0 and 49, Data Integration returns the next century plus the two-digit year from the source string. If the year value of the source string is between 50 and 99, Data Integration returns the current century plus the specified two-digit year.
- Current Year Between 0 and 49. If the current year is between 0 and 49 (such as 2003) and the source string year is between 0 and 49, Data Integration returns the current century plus the two-digit year from the source string. If the source string year is between 50 and 99, Data Integration returns the previous century plus the two-digit year from the source string.
SS
Seconds (0-59).
SSSSS
Seconds since midnight. When you use SSSSS in a TO_DATE expression, Data Integration only evaluates the time portion of a date.
For example, the expression TO_DATE( DATE_STR, ‘MM/DD/YYYY SSSSS’) converts 12/31/1999 3783 to 12/31/1999 01:02:03.
US
Microseconds (0-999999).
Y
The current year on the machine running the Secure Agent with the last digit of the year replaced with the string value.
YY
The current year on the machine running the Secure Agent with the last two digits of the year replaced with the string value.
YYY
The current year on the machine running the Secure Agent with the last three digits of the year replaced with the string value.
YYYY
Four digits of a year. Do not use this format string if you are passing two-digit years. Use the RR or YY format string instead.
Requirements
Data Integration expects the format of the TO_DATE string to meet the following conditions:
•The format of the TO_DATE string must match the format string including any date separators. If it does not, Data Integration might return inaccurate values or skip the row. For example, if you pass the string ‘20200512’, representing May 12, 2020, to TO_DATE, you must include the format string YYYYMMDD. If you do not include a format string, Data Integration expects the string in the default date format MM/DD/YYYY HH24:MI:SS. Likewise, if you pass a string that does not match the format string, Data Integration returns an error and skips the row. For example, if you pass the string 2020120 to TO_DATE and include the format string YYYYMMDD, Data Integration returns an error and skips the row because the string does not match the format string.
• The format string must always be enclosed within single quotation marks.
Tip: By default, Data Integration uses the format string MM/DD/YYYY HH24:MI:SS. The format string is not case sensitive.
Example
The following examples illustrate the J, RR, and SSSSS format strings. See the individual functions for more examples.
J format string
The following expression converts strings in the SHIP_DATE_MJD_STRING field 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.
RR format string
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
YY format string
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, but YY returns the year 1905.
SSSSS format string
The following expression converts strings that include the seconds since midnight to date values: