Format models

A format model is a character literal that describes the format of datetime and numeric data stored in a character string. When you convert a character string into a date or number, a format model determines how TimesTen interprets the string.

Number format models

Use number format models in the following functions:

In the TO_CHAR function to translate a value of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to VARCHAR2 data type.
In the TO_NUMBER function to translate a value of CHAR or VARCHAR2 data type to NUMBER data type.

Number format elements

A number format model is composed of one or more number format elements. The table lists the elements of a number format model. Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

The default american_america NLS language and territory setting is used.

Table 3-1 Number format elements

Element	Example	Description
, (comma)	9,999	Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions A comma element cannot begin a number format model. A comma cannot appear to the right of the decimal character or period in a number format model.
. (period)	99.99	Returns a decimal point, which is a period (.) in the specified position. Restriction You can specify only one period in a format model.
$	$9999	Returns value with leading dollar sign.
0	0999 9990	Returns leading zeros. Returns trailing zeros.
9	9999	Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B	B9999	Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
C	C999	Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
D	99D99	Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.). Restrictions You can specify only one decimal character in a number format model.
EEEE	9.9EEEE	Returns a value in scientific notation.
G	9G999	Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model. Restrictions A group separator cannot appear to the right of a decimal character or period in a number format model.
L	L999	Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).
MI	999MI	Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restrictions The MI format element can appear only in the last position of a number format model.
PR	999PR	Returns negative value in <angle brackets>. Returns positive value with a leading and trailing blank. Restrictions The PR format element can appear only in the last position of a number format model.
RN	RN	Returns a value as Roman numerals in uppercase.
rn	rn	Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
S	S9999	Returns negative value with a leading minus sign (-). Returns positive value with a leading plus sign (+).
S	9999S	Returns negative value with a trailing minus sign (-). Returns positive value with a trailing plus sign (+). Restrictions The S format element can appear only in the first or last position of a number format model.
TM	TM	The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then TimesTen automatically returns the number in scientific notation. Restrictions You cannot precede this element with any other element. You can follow this element only with one 9 or one E or (e), but not with any combination of these. The following statement returns an error: `SELECT TO_NUMBER (1234, 'TM9e') from DUAL;`
U	U9999	Returns in the specified position the euro (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter).
V	999V99	Returns a value multiplied by 10ⁿ (and if necessary, rounds it up), where n is the number of 9s after the V.
X	XXXX	Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then TimesTen rounds it to an integer. Restrictions This element accepts only positive values or 0. Negative values return an error. You can precede this element only with 0 (which returns leading zeros) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has a leading blank.

Datetime format models

Use datetime format models in the following functions:

In the TO_CHAR or TO_DATE functions to translate a character value that is in a format other than the default format for a datetime value.
In the TO_CHAR function to translate a datetime value that is in a format other than the default format into a string.

The total length of a datetime format model cannot exceed 22 characters.

The default american_america NLS language and territory setting is used.

Datetime format elements

A datetime format model is composed of one or more datetime format elements.

Table 3-2 Datetime format elements

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.
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	Returns a value in the 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	Returns a value in the 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.
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).
HH24	Hour of day (0-23).
J	Julian day: The number of days since January 1, 4712 BC. Numbers 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.
RM	Roman numeral month (I-XII. January = I).
RR	Stores 20th century dates in the 21st century using only two digits.
RRRR	Rounds 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.
X	Local radix character. Example: 'HH:MI:SSXFF'.
Y,YYY	Year with comma in this position.
YYYYSYYYY	4-digit year. S prefixes BC dates with a minus sign.
YYYYYY	Last 3, 2, or 1 digit (s) of year.

Format model for ROUND and TRUNC date functions

The table lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model 'DD' returns the date rounded or truncated to the day with a time of midnight.

Format mode	Rounding or truncating unit
CC SCC	One greater than the first two digits of a four-digit year
SYYYYYYYYYEARSYEARYYYYYY	Year
IYYYIYIYI	ISO Year
Q	Quarter
MONTHMONMMRM	Month
WW	Same day of the week as the first day of the year
IW	Same day of the week as the first day of the ISO year
W	Same day of the week as the first day of the month
DDDDDJ	Day
DAYDYD	Starting day of the week
HHHH12HH24	Hour
MI	Minute

Format model for TO_CHAR of TimesTen datetime data types

Use this format model when invoking the TO_CHAR function to convert a datetime value of TT_TIMESTAMP or TT_DATE. In addition, use this format model when invoking the TO_CHAR function to convert any numeric value other than NUMBER or ORA_FLOAT.

If a numeric value does not fit in the specified format, TimesTen truncates the value.
The format string cannot exceed 50 characters.
D always results in a decimal point. Its value cannot be changed with an NLS parameter.

If a float with an absolute value less than 1e-126 or greater than 1e126 is specified as input to the TO_CHAR function, TimesTen returns an error.

Format	Description
DD	Day of month (1-31)
MM	Month (1-12)
MON	Month (three character prefix)
MONTH	Month (full name blank-padded to 9 characters)
YYYY	Year (four digits)
Y,YYY	Year (with comma as shown)
YYY	Year (last three digits)
YY	Year (last two digits)
Y	Year (last digit)
Q	Quarter
HH	Hour (1-12)
HH12	Hour (1-12)
HH24	Hour (0-23)
MI	Minute (0-59)
SS	Second (0-59)
FF	Fractions of a second to a precision of 6 digits
FFn	Fractions of a second to the precision specified by n
AM	Meridian indicator
A.M.	Meridian indicator
PM	Meridian indicator
P.M.	Meridian indicator
- / , . ; :	Punctuation to be output
"text"	Text to be output
9	Digit
0	Leading/trailing zero
.	Decimal point
,	Comma
EEEE	Scientific notation
S	Sign mode
B	Blank mode. If there are no digits, the string is filled with blanks.
FM	No-blank mode (Fill mode). If this element is used, trailing and/or leading spaces are suppressed.
$	Leading dollar sign.