15.00 - TO_DATE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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
A.D.

AD indicator.

AM
A.M.

Meridian indicator.

BC
B.C.

BC indicator.

CC
SCC

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
HH12

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
IY
I

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
P.M.

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
SYEAR

Year, spelled out. S prefixes BC dates with a minus sign.

Note: YEAR and SYEAR are only supported by the TO_CHAR function.

YYYY
SYYYY

4-digit year. S prefixes BC dates with a minus sign.

YYY
YY
Y

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.