quoted from:
Format Models
A format model is a character literal that describes the format of datetime or numeric data stored in a character string. A format model does not change the internal representation of the value in the database. When you convert a character string into a date or number, a format model determines how Oracle Database interprets the string. In SQL statements, you can use a format model as an argument of the TO_CHAR
and TO_DATE
functions to specify:
-
The format for Oracle to use to return a value from the database
-
The format for a value you have specified for Oracle to store in the database
For example:
-
The datetime format model for the string '
17:45:29
' is 'HH24:MI:SS
'. -
The datetime format model for the string '
11-Nov-1999
' is 'DD-Mon-YYYY
'. -
The number format model for the string '
$2,304.25
' is '$9,999.99
'.
For lists of number and datetime format model elements, see Table 2-15 and Table 2-17.
The values of some formats are determined by the value of initialization parameters. For such formats, you can specify the characters returned by these format elements implicitly using the initialization parameter NLS_TERRITORY
. You can change the default date format for your session with the ALTER
SESSION
statement.
See Also:
-
ALTER SESSION for information on changing the values of these parameters and Format Model Examples for examples of using format models
-
Oracle Database Reference and Oracle Database Globalization Support Guide for information on these parameters
This remainder of this section describes how to use the following format models:
Number Format Elements
A number format model is composed of one or more number format elements. The tables that follow list the elements of a number format model and provide some examples.
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.
Table 2-15 Number Format Elements
Element | Example | Description |
---|---|---|
, (comma) |
| Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:
|
. (period) |
| Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model. |
$ |
| Returns value with a leading dollar sign. |
0 |
| Returns leading zeros. Returns trailing zeros. |
9 |
| 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 |
| Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model). |
| Returns in the specified position the ISO currency symbol (the current value of the | |
| Returns in the specified position the decimal character, which is the current value of the Restriction: You can specify only one decimal character in a number format model. | |
| Returns a value using in scientific notation. | |
| Returns in the specified position the group separator (the current value of the Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model. | |
| Returns in the specified position the local currency symbol (the current value of the | |
MI |
| Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restriction: The MI format element can appear only in the last position of a number format model. |
PR |
| Returns negative value in <angle brackets>. Returns positive value with a leading and trailing blank. Restriction: 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. Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999. |
S |
| Returns negative value with a leading minus sign (-). Returns positive value with a leading plus sign (+). Returns negative value with a trailing minus sign (-). Returns positive value with a trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model. |
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 Oracle Database automatically returns the number in scientific notation. Restrictions:
|
| Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the | |
V |
| Returns a value multiplied by 10n (and if necessary, round it up), where |
| Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer. Restrictions:
|
Table 2-16 shows the results of the following query for different values of number
and 'fmt'
:
CopySELECT TO_CHAR(number, 'fmt') FROM DUAL;
Table 2-16 Results of Number Conversions
number | 'fmt' | Result |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 2-17 Datetime Format Elements
Element | TO_* datetime functions? | Description |
---|---|---|
- / , . ; : "text" | Yes | Punctuation and quoted text is reproduced in the result. |
AD A.D. | Yes | AD indicator with or without periods. |
AM A.M. | Yes | Meridian indicator with or without periods. |
BC B.C. | Yes | BC indicator with or without periods. |
CC SCC |
| Century.
For example, 2002 returns 21; 2000 returns 20. |
D | Yes | Day of week (1-7). This element depends on the NLS territory of the session. |
| Yes | Name of day. |
DD | Yes | Day of month (1-31). |
DDD | Yes | Day of year (1-366). |
DL | Yes | Returns a value in the long date format, which is an extension of the Oracle Database Restriction: You can specify this format only with the |
DS | 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 | Yes | Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE | Yes | Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
| 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 data type or the data type's default precision. Valid in timestamp and interval formats, but not in Examples:
|
| Yes | Returns a value with no leading or trailing blanks. See Also: FM |
| Yes | Requires exact matching between the character data and the format model. See Also: FX |
HH HH12 | Yes | Hour of day (1-12). |
HH24 | Yes | Hour of day (0-23). |
IW |
| Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard.
|
IYYY |
| 4-digit year of the year containing the calendar week, as defined by the ISO 8601 standard. |
IYY IY I |
| Last 3, 2, or 1 digit(s) of the year containing the calendar week, as defined by the ISO 8601 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. |
PM P.M. | Yes | Meridian indicator with or without periods. |
Q |
| 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: The RR Datetime Format Element |
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). |
TS | Yes | 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 saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in 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 region names supported in the database. Valid in timestamp and interval formats, but not in 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 | Yes | Local radix character. Example: |
Y,YYY | Yes | Year with comma in this position. |
YEAR SYEAR |
| Year, spelled out; |
YYYY SYYYY | Yes | 4-digit year; |
YYY YY Y | Yes | Last 3, 2, or 1 digit(s) of year. |