| Element | Description |
|---|
-
/
,
.
;
:
"text"
| Punctuation and quoted text is 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
| Century. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.
For example, 2002 returns 21; 2000 returns 20. |
D
| Day of week (1-7). |
DAY
| Name of day, padded with blanks to display width of the widest name of day in the date language used for this element. |
DD
| Day of month (1-31). |
DDD
| Day of year (1-366). |
| Returns a value in the long date format, which is an extension of Oracle Database's DATE format (the current value of the NLS_DATE_FORMAT parameter). Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy'. Restriction: You can specify this format only with the TS element, separated by white space. |
| Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGEparameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR'. Restriction: You can specify this format only with the TS element, separated by white space. |
DY
| Abbreviated name of day. |
E
| Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE
| Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
FF [1..9]
| Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime datatype or the datatype's default precision. Examples: 'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
|
FM
| Returns a value with no leading or trailing blanks. |
FX
| Requires exact matching between the character data and the format model. |
HH
| Hour of day (1-12). |
HH12
| Hour of day (1-12). |
HH24
| Hour of day (0-23). |
IW
| Week of year (1-52 or 1-53) based on the ISO standard. |
IYY
IY
I
| Last 3, 2, or 1 digit(s) of ISO year. |
IYYY
| 4-digit year based on the ISO standard. |
J
| Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers. |
MI
| Minute (0-59). |
MM
| Month (01-12; January = 01). |
MON
| Abbreviated name of month. |
MONTH
| Name of month, padded with blanks to display width of the widest name of month in the date language used for this element. |
PM
P.M.
| Meridian indicator with or without periods. |
Q
| Quarter of year (1, 2, 3, 4; January - March = 1). |
RM
| Roman numeral month (I-XII; January = I). |
RR
| Lets you store 20th century dates in the 21st century using only two digits. |
RRRR
| Round 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). |
| Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters. Restriction: You can specify this format only with the DL or DS element, separated by white space. |
TZD | Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR. Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time). |
TZH
| Time zone hour. (See TZM format element.) Example: 'HH:MI:SS.FFTZH:TZM'. |
TZM
| Time zone minute. (See TZH format element.) Example: 'HH:MI:SS.FFTZH:TZM'. |
TZR
| Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific |
WW
| Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
W
| Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
X
| Local radix character. Example: 'HH:MI:SSXFF'. |
Y,YYY
| Year with comma in this position. |
YEAR
SYEAR
| 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. |
