TO_DATE
Purpose
Converts string_expr to a DATE data type.
TO_DATE does not convert data to any of the other datetime data types. Do not use the TO_DATE function with a DATE value for string_expr. The first two digits of the returned DATE value can differ from the original string_expr depending on format_arg or the default date format.
Syntax
where:
Syntax element … |
Specifies … |
TD_SYSFNLIB |
the name of the database where the function is located. |
string_expr |
a character argument. If the conversion fails, TO_DATE returns an error. |
format_arg |
a character argument. format_arg is a date format string specifying the format of string_expr. If format_arg is omitted, the following default date format is used: YYYY-MM-DD If format_arg is NULL, TO_DATE returns NULL. If format_arg is not valid, an error is returned. |
format_arg Format Elements
Element |
Description |
- / , . ; : “text” |
Punctuation characters are ignored and text enclosed in quotation marks is ignored. |
AD |
AD indicator. |
AM |
Meridian indicator. |
BC |
BC indicator. |
CC |
Century. If the last 2 digits of a 4-digit year are between 01 and 99 inclusive, the century is 1 greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, the century is the same as the first 2 digits of that year. Note: CC and SCC are only supported by the TO_CHAR function. |
D |
Day of week (1-7). |
DAY |
Name of day. |
DD |
Day of month (1-31). |
DDD |
Day of year (1-366). |
DL |
Date Long. Equivalent to the format string ‘FMDay, Month FMDD, YYYY’. Note: DL is only supported by the TO_CHAR function. |
DS |
Date Short. Equivalent to the format string ‘FMMM/DD/YYYYFM’. Note: DS is only supported by the TO_CHAR function. |
DY |
abbreviated name of day. |
E |
not supported. |
EE |
not supported. |
FF [1..9] |
Fractional seconds. Use [1..9] to specify the number of fractional digits. FF without any number following it prints a decimal followed by digits equal to the number of fractional seconds in the input data type. If the data type has no fractional digits, FF prints nothing. Note: Any fractional digits beyond 6 digits are truncated. |
FM |
Format Minimum mode. The value is returned with no leading or trailing blanks. This may be toggled on and off by adding an 'FM' before and after a section that should use minimum space. Note: FM is only supported by the TO_CHAR function. |
FX |
Requires the character data and the format model to be an exact match. Note: FX is not supported by the TO_CHAR function. |
HH |
Hour of day (1-12). |
HH24 |
Hour of the day (0-23). |
IW |
Week of year (1-52 or 1-53) based on ISO model. Note: IW is only supported by the TO_CHAR function. |
IYY |
Last 3, 2, or 1 digits of ISO year. Note: I, IY, and IYY are only supported by the TO_CHAR function. |
IYYY |
4-digit year based on the ISO standard. Note: IYYY is only supported by the TO_CHAR function. |
J |
Julian day, the number of days since January 1, 4713 BC. Number specified with J must be integers. Teradata uses the Gregorian calendar in calculations to and from Julian Days. |
MI |
Minute (0-59). |
MM |
Month (01-12). |
MON |
Abbreviated name of month. |
MONTH |
Name of month. |
PM |
Meridian indicator. |
Q |
Quarter of year (1, 2, 3, 4). Note: Q is only supported by the TO_CHAR function. |
RM |
Roman numeral month (I - XII). |
RR |
Stores 20th century dates in the 21st century using only 2 digits. If the current year and the specified year are both in the range of 0-49, the date is in the current century. Note: RR is not supported by the TO_CHAR function. |
RRRR |
Round year. Accepts either 4-digit or 2-digit input. 2-digit input provides the same return as RR. Note: RRRR is not supported by the TO_CHAR function. |
SP |
Spelled. Any numeric element followed by SP is spelled in English words. The words are capitalized according to how the element is capitalized. For example: 'DDDSP' specifies all uppercase, 'DddSP' specifies that the first letter is capitalized, and 'dddSP' specifies all lowercase. Note: SP is only supported by the TO_CHAR function. |
SS |
Second (0-59). |
SSSSS |
Seconds past midnight (0-86399). |
TS |
Time Short. Equivalent to the format string 'HH:MI:SS AM'. Note: TS is only supported by the TO_CHAR function. |
TZD |
not supported. |
TZH |
Time zone hour. |
TZM |
Time zone minute. |
TZR |
Time zone region. Equivalent to the format string 'TZH:TZM'. Note: TZR is only supported by the TO_CHAR function. |
WW |
Week of year (1-53) where week 1 starts on the first day of the year and continues to the 7th day of the year. Note: WW is only supported by the TO_CHAR function. |
W |
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. Note: W is only supported by the TO_CHAR function. |
X |
Local radix character. |
Y,YYY |
Year with comma in this position. |
YEAR |
Year, spelled out. S prefixes BC dates with a minus sign. Note: YEAR and SYEAR are only supported by the TO_CHAR function. |
YYYY |
4-digit year. S prefixes BC dates with a minus sign. |
YYY |
Last 3, 2, or 1 digit of year. If the current year and the specified year are both in the range of 0-49, the date is in the current century. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Invocation
TO_DATE is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.
Argument Types and Rules
Expressions passed to this function must be a VARCHAR data type.
You can also pass arguments with data types that can be converted to the above type using the implicit data type conversion rules that apply to UDFs.
Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.
For details, see “Compatible Types” in SQL External Routine Programming.
Result Type
TO_DATE is an overloaded scalar function whose return value data type is DATE.
Example
The following query:
SELECT TO_DATE ('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH: MI A.M.');
returns the result 15-JAN-89.