15.00 - TO_DATE - Teradata Database

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.

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.