Format Models

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
	0999	Returns value with leading zeros
	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.