Datetime Format Elements

A datetime format model is composed of one or more datetime format elements. Table 3-2 shows the datatime format elements. In addition:

• For input format models, the format elements cannot appear more than once, and format elements that represent similar information cannot be combined. For example, you cannot use the SYYYY and the BC format elements in the same format string.

• For DATE format elements, capitalization in a spelled-out word, abbreviation or Roman numeral, follows the capitalization in the corresponding format element. For example, the date format model DAY produces capitalized words, like MONDAY, whereas Day produces Monday, and day produces monday.

Table 3-2 Datetime format elements

Element	Description
-/,.;:"text"	Punctuation and quoted text are reproduced in the result.
AD A.D.	AD indicator with or without periods.
AM A.M.	Meridian indicator with or without periods.
BC B.C.	BC indicator with or without periods.
CC SCC	Valid in TO_CHAR. Century: If the last two digits of a 4-digit year are between 01 and 99 (inclusive), the century is one greater than the first two digits of the year. If the last two digits of a 4-digit year are 00, the century is the same as the first two digits of that year. For example, 2002 returns 21 and 2000 returns 20.
D	Day of week (1-7).
DAY	Name of day, padded with blanks to display width of widest name of day.
DD	Day of month (1-31).
DDD	Day of year.
DL	Long date format. In the default AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. Restriction: Specify this format only with the TS element, separated by white space.
DS	Short date format In the default AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. Restriction: Specify this format only with the TS element, separated by white space.
DY	Abbreviated name of day.
FF[1-9]	Valid in TO_TIMESTAMP. Fractional seconds. No radix character is printed. Use the X format element to add the radix character. Use the 1 to 9 numbers after FF to specify the number of digits in the fractional second part of the datatime value returned. If you do not specify a digit, the value is determined by the precision specified for the datetime data type, or by the data type's default precision.
FM	Returns a value with no leading or no trailing blanks.
FX	Returns exact matching between the character data and the format model.
HH HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
IW	Valid in TO_CHAR. Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard. A calendar week starts on Monday. The first calendar week of the year includes January 4. The first calendar week of the year may include December 29, 30, and 31. The last calendar week of the year may include January 1, 2, and 3.
IYYY	Valid in TO_CHAR. 4-digit year of the year containing the calendar week, as defined by the ISO 8601 standard.
IYY IY I	Valid in TO_CHAR. Last 3, 2, or 1 digit(s) of the year containing the calendar week, as defined by the ISO 8601 standard.
J	Julian day. This is the number of days since January 1, 4712 BC. Numbers specified with J must be integers.
MI	Minute (0-59).
MM	Month (01-12, where January = 01).
MON	Abbreviated name of month.
MONTH	Name of month padded with blanks to display width of the widest name of month.
PM P.M.	Meridian indicator with or without periods.
Q	Valid in TO_CHAR. Quarter of the year (1, 2, 3, 4). January = 1.
RM	Roman numeral month (I-XII. January = I).
RR	For 20th century dates in the 21st century using only two digits.
RRRR	Rounded year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
TS	Returns a value in the short time format. Restriction: Specify this format only with the DL or DS element, separated by white space.
WW	Valid in TO_CHAR. Week of year (1-53) where week one starts on the first day of the year and continues to the seventh day of the year.
W	Valid in TO_CHAR. Week of month (1-5) where week one starts on the first day of the month and ends on the seventh.
X	Local radix character For example: 'HH:MI:SSXFF'
Y,YYY	Year with comma in this position
YEAR SYEAR	Valid in TO_CHAR. Year, spelled out. S prefixes BC dates with a minus sign (-).
YYYY SYYYY	4-digit year. S prefixes BC dates with a minus sign.
YYY YY Y	Last 3, 2, or 1 digit(s) of year.