TO_DATE and IS_DATE Format Strings
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 the predefined formats.
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. The date separator need not match for date validation to take place. If any part does not match, the Data Integration Service does not convert the string, and it skips the row. If you omit the format string, the source string must be in the date format specified in the session.
IS_DATE indicates whether a value is a valid date. A valid date is any string in the date format specified in the session. If the strings that you want to test are not in the specified date format, use the format of the strings listed in "TO_DATE and IS_DATE Format Strings" table. If the format of a string does not match the specified format or if the string does not represent a valid date, the function returns FALSE (0). If the format of the string matches the specified format of the string and is a valid date, the function returns TRUE (1). IS_DATE format strings are not internationalized and must be entered in one of the formats listed in the following table.
The following table lists 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.000000000. For example, the expression TO_DATE(‘2451544’, ‘J’) converts 2451544 to Dec 31 1999 00:00:00.000000000. |
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 (0-999999999). |
RR | 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, the Data Integration Service 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, the Data Integration Service 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, the Data Integration Service returns the current century plus the two-digit year from the source string. If the source string year is between 50 and 99, the Data Integration Service 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, the Data Integration Service 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 node running the Data Integration Service with the last digit of the year replaced with the string value. |
YY | The current year on the node running the Data Integration Service with the last two digits of the year replaced with the string value. |
YYY | The current year on the node running the Data Integration Service 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. |
Rules and Guidelines for Date Format Strings
Use the following rules and guidelines when you work with date format strings:
- •The format of the TO_DATE string must match the format string. If it does not, the Data Integration Service 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, the Data Integration Service expects the string in the date format specified in the session. Likewise, if you pass a string that does not match the format string, the Data Integration Service returns an error and skips the row. For example, if you pass the string 2020120 to TO_DATE and include the format string YYYYMMDD, the Data Integration Service returns an error and skips the row because the string does not match the format string.
- •The format string must be enclosed within single quotation marks.
- •The Data Integration Service uses the default date time format specified in the session. Default is MM/DD/YYYY HH24:MI:SS.US. 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 port 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.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.
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.000000000 |
08/17/05 | 08/17/2005 00:00:00.000000000 |
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.000000000 |
08/17/05 | 08/17/1905 00:00:00.000000000 |
SSSSS Format String
The following expression converts strings that include 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.000000000 |
09/15/1996 86399 | 09/15/1996 23:59:59.000000000 |