format_arg Format Elements - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™
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.

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’.
DL is only supported by the TO_CHAR function.
DS Date Short. Equivalent to the format string ‘FMMM/DD/YYYYFM’.
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.

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.
FM is only supported by the TO_CHAR function.
FX Requires the character data and the format model to be an exact match.
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.
IW is only supported by the TO_CHAR function.
IYY

IY

I

Last 3, 2, or 1 digits of ISO year.
I, IY, and IYY are only supported by the TO_CHAR function.
IYYY 4-digit year based on the ISO standard.
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).
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.
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.
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.

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'.
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'.
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.
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.
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.
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.