- Operators
- Functions
- Format Models
- Reserved Words
- Priviledges
This chapter is from the book
This chapter is from the book
This chapter is from the book
Date Format Models
Table 1-15. Date Format Models
|
Element |
Value Returned |
|---|---|
|
- / , . ; “text” |
Quoted text and punctuation are reproduced in the result |
|
AD A.D. |
Indicates date that is AD. Periods optional |
|
AM A.M. PM P.M. |
Before or after noon. Periods optional |
|
BC B.C. |
Indicates date that is BC. Periods optional |
|
CC SCC |
Century (SCC precedes BC century with -) |
|
D |
The day of week (1–7) |
|
DAY |
The name of the day of the week (Monday, Tuesday, etc.). Padded to 9 characters. |
|
DD |
Day of month (1–31) |
|
DDD |
The number of the day of year (1–366) |
|
DY |
The name of the day of the week, abbreviated |
|
E |
Abbreviated era name (for Japanese Imperial, ROC Official, and Thai Buddha calendars) |
|
EE |
Full era name |
|
FF [1–9] |
Fractional seconds. 1–9 specifies the number of digits |
|
HH |
Hour of day(12-hour clock) |
|
HH12 |
Hour of day (12-hour clock) |
|
HH24 |
Hour of day (24-hour clock) |
|
IW |
Number of Week of the year |
|
IYY IY I |
Last 3, 2, or 1 digit(s) of ISO year |
|
IYYY |
4-digit ISO year |
|
J |
Julian day(number of days since January 1, 4712 BC) |
|
MI |
Minute (0–59) |
|
MM |
Month (01–12) |
|
MON |
JAN, FEB, MAR, etc. |
|
MONTH |
Full month name, padded to 9 characters |
|
Q |
Quarter of year where JAN–MAR = 1 |
|
RM |
Month in Roman numerals (I–XII; JAN = I) |
|
RR |
Last two digits of the year, for years in previous or next century (where previous if current year is <=50, next if current year >50) |
|
RRRR |
Round year. Accepts 4 or 2 digit input, 2 digit returns as RR. |
|
SS |
Seconds (0–59) |
|
SSSSS |
Seconds past midnight (0–86399) |
|
TZD |
Abbreviated Time Zone String with Daylight Savings |
|
TZH |
Time zone hour |
|
TZM |
Time zone minute |
|
WW |
The week of the year (1–53) |
|
W |
The week of the month |
|
X |
Local radix character |
|
Y, YYY |
Year, with comma as shown |
|
YEAR SYEAR |
Year, fully spelled out. For SYEAR, BC dates use “-” |
|
Y YY YYY |
Final one, two, or three digits of the year |
Date Prefixes and Suffixes
The following prefixes can be added to date formats:
|
FM |
The fill mode toggle. Suppresses blank padding of MONTH or DAY |
|
FX |
Specifies that the format of TO_DATE functions must be an exact match |
The following suffixes may be added to date formats:
|
TH |
converts to an ordinal number ("5TH") |
|
SP |
Spells out the number ("FIVE") |
|
SPTH or THSP |
Spells out the ordinal number ("FIFTH") |
Number Format Models
Table 1-16. Number Format Models
|
Element |
Example |
Value Returned |
|---|---|---|
|
, |
9,999 |
Returns a comma at the position specified |
|
. |
99.99 |
Returns a period (decimal point) at the position specified |
|
$ |
$9999 |
Leading dollar sign |
|
0 |
0999 |
Returns value with leading zeros |
|
0 |
9990 |
Returns value with trailing zeros |
|
9 |
9999 |
Returns value with the specified number of digits. Leading space if positive, – if negative. Leading zeros are blank, except when integer portion is zero, then a single leading zero is returned. |
|
B |
B9999 |
As in 9, above, but returns a blank in all cases for leading zeros |
|
C |
C999 |
Returns the ISO currency symbol |
|
D |
99D99 |
Returns the NLS decimal character in the specified position |
|
EEEE |
9.9EEEE |
Returns value in scientific notation |
|
FM |
FM90.9 |
Returns a value without leading or trailing blanks |
|
G |
9G999 |
Returns the value with the NLS group separator in the specified position |
|
L |
L999 |
Returns the value with the NLS Local Currency Symbol in the specified position. Negative values have a trailing minus sign (–), positive values with a trailing blank. |
|
PR |
9999PR |
Returns negative values in <angle brackets>, positives have leading and trailing blanks |
|
RN rn |
RN rn |
Returns the value as Roman numerals, in the case-specified |
|
S |
S9999 9999S |
Returns the value with a + or – sign denoting positive or negative value in the position shown (can only be first or last position). |
|
TM |
TM |
“Text minimum.” Returns the smallest number of characters possible and is case-insensitive. Default is TM9 that uses fixed notation up to 64 characters, then scientific notation. |
|
U |
U9999 |
Returns the “Euro” (or other) NLS dual currency symbol in the specified position |
|
V |
999V99 |
Returns a value multiplied by 10 times the number of 9s specified after the V |
|
X |
XXXX |
Returns the Hexadecimal value. Precede with a 0 to have leading zeros, or FM to remove the leading blank. |