Element | Specify in TO_* datetime functions? | Description |
---|---|---|
- | Yes | Punctuation and quoted text is reproduced in the result. |
AD | Yes | AD indicator with or without periods. |
AM | Yes | Meridian indicator with or without periods. |
BC | Yes | BC indicator with or without periods. |
CC | No | Century.
For example, 2002 returns 21; 2000 returns 20. |
D | Yes | Day of week (1-7). |
DAY | Yes | Name of day, padded with blanks to display width of the widest name of day in the date language used for this element. |
DD | Yes | Day of month (1-31). |
DDD | Yes | Day of year (1-366). |
| Yes | Returns a value in the long date format, which is an extension of Oracle Database's Restriction: You can specify this format only with the |
| Yes | 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 Restriction: You can specify this format only with the |
DY | Yes | Abbreviated name of day. |
E | No | Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE | No | Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
FF [1..9] | Yes | 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:
|
FM | Yes | Returns a value with no leading or trailing blanks. See Also : Additional discussion on this format model modifier in the Oracle Database SQL Reference |
FX | Yes | Requires exact matching between the character data and the format model. See Also : Additional discussion on this format model modifier in the Oracle Database SQL Reference |
HH | Yes | Hour of day (1-12). |
HH12 | No | Hour of day (1-12). |
HH24 | Yes | Hour of day (0-23). |
IW | No | Week of year (1-52 or 1-53) based on the ISO standard. |
IYY | No | Last 3, 2, or 1 digit(s) of ISO year. |
IYYY | No | 4-digit year based on the ISO standard. |
J | Yes | Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers. |
MI | Yes | Minute (0-59). |
MM | Yes | Month (01-12; January = 01). |
MON | Yes | Abbreviated name of month. |
MONTH | Yes | Name of month, padded with blanks to display width of the widest name of month in the date language used for this element. |
PM | No | Meridian indicator with or without periods. |
Q | No | Quarter of year (1, 2, 3, 4; January - March = 1). |
RM | Yes | Roman numeral month (I-XII; January = I). |
RR | Yes | Lets you store 20th century dates in the 21st century using only two digits. See Also: Additional discussion on |
RRRR | Yes | 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 | Yes | Second (0-59). |
SSSSS | Yes | 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 Restriction: You can specify this format only with the |
TZD | Yes | 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: |
TZH | Yes | Time zone hour. (See Example: |
TZM | Yes | Time zone minute. (See Example: |
TZR | Yes | Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific |
WW | No | 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 | No | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
X | Yes | Local radix character. Example: |
Y,YYY | Yes | Year with comma in this position. |
YEAR | No | Year, spelled out; |
YYYY | Yes | 4-digit year; |
YYY | Yes | Last 3, 2, or 1 digit(s) of year. |
excerpt from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#BABEFHFG