Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
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-17, "Matching Character Data and Format Models with the FX Format Model Modifier" and Table 2-19, "Attributes of the XMLFormat Object".
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:
|
This remainder of this section describes how to use:
You can use number format models in the following functions:
In the TO_CHAR
function to translate a value of NUMBER
, BINARY_FLOAT
, or BINARY_DOUBLE
datatype to VARCHAR2
datatype
In the TO_NUMBER
function to translate a value of CHAR
or VARCHAR2
datatype to NUMBER
datatype
In the TO_BINARY_FLOAT
and TO_BINARY_DOUBLE
functions to translate CHAR
and VARCHAR2
expressions to BINARY_FLOAT
or BINARY_DOUBLE
values
All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. This event typically occurs when you are using TO_CHAR
with a restrictive number format string, causing a rounding operation.
If a positive NUMBER
value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative NUMBER
value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).
If a BINARY_FLOAT
or BINARY_DOUBLE
value is converted to CHAR
or NCHAR
, and the input is either infinity or NaN
(not a number), then Oracle always returns the pound signs to replace the value.
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-13 Number Format Elements
Table 2-14 shows the results of the following query for different values of number
and 'fmt'
:
SELECT TO_CHAR(number, 'fmt') FROM DUAL;
Table 2-14 Results of Number Conversions
number | 'fmt' | Result |
---|---|---|
-1234567890 |
9999999999S |
'1234567890-' |
0 |
99.99 |
' .00' |
+0.1 |
99.99 |
' .10' |
-0.2 |
99.99 |
' -.20' |
0 |
90.99 |
' 0.00' |
+0.1 |
90.99 |
' 0.10' |
-0.2 |
90.99 |
' -0.20' |
0 |
9999 |
' 0' |
1 |
9999 |
' 1' |
0 |
B9999 |
' ' |
1 |
B9999 |
' 1' |
0 |
B90.99 |
' ' |
+123.456 |
999.999 |
' 123.456' |
-123.456 |
999.999 |
'-123.456' |
+123.456 |
FM999.009 |
'123.456' |
+123.456 |
9.9EEEE |
' 1.2E+02' |
+1E+123 |
9.9EEEE |
' 1.0E+123' |
+123.456 |
FM9.9EEEE |
'1.2E+02' |
+123.45 |
FM999.009 |
'123.45' |
+123.0 |
FM999.009 |
'123.00' |
+123.45 |
L999.99 |
' $123.45' |
+123.45 |
FML999.99 |
'$123.45' |
+1234567890 |
9999999999S |
'1234567890+' |
You can use datetime format models in the following functions:
In the TO_*
datetime functions to translate a character value that is in a format other than the default format into a datetime value. (The TO_
* datetime functions are TO_CHAR
, TO_DATE
, TO_TIMESTAMP
, TO_TIMESTAMP_TZ
, TO_YMINTERVAL
, and TO_DSINTERVAL
.)
In the TO_CHAR
function to translate a datetime value that is in a format other than the default format into a string (for example, to print the date from an application)
The total length of a datetime format model cannot exceed 22 characters.
The default datetime formats are specified either explicitly with the initialization parameter NLS_DATE_FORMAT
or implicitly with the initialization parameter NLS_TERRITORY
. You can change the default datetime formats for your session with the ALTER
SESSION
statement.
See Also: ALTER SESSION and Oracle Database Globalization Support Guide for information on the NLS parameters |
A datetime format model is composed of one or more datetime format elements as listed in Table 2-19, "Attributes of the XMLFormat Object".
For input format models, format items cannot appear twice, and format items that represent similar information cannot be combined. For example, you cannot use 'SYYYY' and 'BC' in the same format string.
Some of the datetime format elements cannot be used in the TO_*
datetime functions, as noted in Table 2-19.
The following datetime format elements can be used in timestamp and interval format models, but not in the original DATE
format model: FF
, TZD
, TZH,
TZM
, and TZR
.
Many datetime format elements are blank padded to a specific length. Please refer to the format model modifier FM for more information.
Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the date format model 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.
You can include these characters in a date format model:
Punctuation such as hyphens, slashes, commas, periods, and colons
Character literals, enclosed in double quotation marks
These characters appear in the return value in the same location as they appear in the format model.
Table 2-15 Datetime Format Elements
Element | Specify in 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 |
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 length of 9 characters. |
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 Oracle Database's DATE format (the current value of the NLS_DATE_FORMAT parameter). Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy' . In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy '.
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 NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR '. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR '.
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 IY I |
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 length of 9 characters. |
PM P.M. |
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). |
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 NLS_TERRITORY and NLS_LANGUAGE initialization parameters.
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 TZM format element.)
Example: |
TZM |
Yes | Time zone minute. (See TZH format element.)
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 SYEAR |
No | Year, spelled out; S prefixes BC dates with a minus sign (-). |
YYYY SYYYY |
Yes | 4-digit year; S prefixes BC dates with a minus sign. |
YYY YY Y |
Yes | Last 3, 2, or 1 digit(s) of year. |
Oracle returns an error if an alphanumeric character is found in the date string where a punctuation character is found in the format string. For example, the following format string returns an error:
TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')
The functionality of some datetime format elements depends on the country and language in which you are using Oracle Database. For example, these datetime format elements return spelled values:
MONTH
MON
DAY
DY
BC or AD or B.C. or A.D.
AM or PM or A.M or P.M.
The language in which these values are returned is specified either explicitly with the initialization parameter NLS_DATE_LANGUAGE
or implicitly with the initialization parameter NLS_LANGUAGE
. The values returned by the YEAR
and SYEAR
datetime format elements are always in English.
The datetime format element D
returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY
.
See Also: Oracle Database Reference and Oracle Database Globalization Support Guide for information on globalization support initialization parameters |
Oracle calculates the values returned by the datetime format elements IYYY, IYY, IY, I, and IW according to the ISO standard. For information on the differences between these values and those returned by the datetime format elements YYYY, YYY, YY, Y, and WW, see the discussion of globalization support in Oracle Database Globalization Support Guide.
The RR
datetime format element is similar to the YY
datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR
datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.
If you use the TO_DATE
function with the YY
datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR
datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.
That is:
If the specified two-digit year is 00 to 49, then
If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
If the specified two-digit year is 50 to 99, then
If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
The following examples demonstrate the behavior of the RR datetime format element.
Assume these queries are issued between 1950 and 1999:
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 1998 SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 2017
Now assume these queries are issued between 2000 and 2049:
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 1998 SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 2017
Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR datetime format element lets you write SQL statements that will return the same values from years whose first two digits are different.
Table 2-16 lists suffixes that can be added to datetime format elements:
Table 2-16 Date Format Element Suffixes
Suffix | Meaning | Example Element | Example Value |
---|---|---|---|
TH | Ordinal Number | DDTH |
4TH |
SP | Spelled Number | DDSP |
FOUR |
SPTH or THSP | Spelled, ordinal number | DDSPTH |
FOURTH |
Notes on date format element suffixes:
When you add one of these suffixes to a datetime format element, the return value is always in English.
Datetime suffixes are valid only to format output. You cannot use them to insert a date into the database.
The FM
and FX
modifiers, used in format models in the TO_CHAR
function, control blank padding and exact format checking.
A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then reenabled for the portion following its third, and so on.
FM Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE
is AMERICAN
, the largest element for MONTH
is SEPTEMBER
, so all values of the MONTH
format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR
function:
In a datetime format element of a TO_CHAR
function, this modifier suppresses blanks in subsequent character elements (such as MONTH
) and suppresses leading zeroes for subsequent number elements (such as MI
) in a date format model. Without FM
, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM
, which suppresses blank padding, the length of the return value may vary.
In a number format element of a TO_CHAR
function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM
, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.
FX Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE
function:
Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
The character argument cannot have extra blanks. Without FX
, Oracle ignores extra blanks.
Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX
, numbers in the character argument can omit leading zeroes.
When FX
is enabled, you can disable this check for leading zeroes by using the FM
modifier as well.
If any portion of the character argument violates any of these conditions, then Oracle returns an error message.
The following statement uses a date format model to return a character expression:
SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR (SYSDATE, 'fmMonth')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides" FROM DUAL; Ides ------------------ 3RD of April, 1998
The preceding statement also uses the FM
modifier. If FM
is omitted, then the month is blank-padded to nine characters:
SELECT TO_CHAR(SYSDATE, 'DDTH')||' of '|| TO_CHAR(SYSDATE, 'Month')||', '|| TO_CHAR(SYSDATE, 'YYYY') "Ides" FROM DUAL; Ides ----------------------- 03RD of April , 1998
The following statement places a single quotation mark in the return value by using a date format model that includes two consecutive single quotation marks:
SELECT TO_CHAR(SYSDATE, 'fmDay')||'''s Special' "Menu" FROM DUAL; Menu ----------------- Tuesday's Special
Two consecutive single quotation marks can be used for the same purpose within a character literal in a format model.
Table 2-17 shows whether the following statement meets the matching conditions for different values of char
and 'fmt
' using FX
(the table named table
has a column date_column
of datatype DATE
):
UPDATE table SET date_column = TO_DATE(char, 'fmt');
Table 2-17 Matching Character Data and Format Models with the FX Format Model Modifier
char | 'fmt' | Match or Error? |
---|---|---|
'15/ JAN /1998' |
'DD-MON-YYYY' |
Match |
' 15! JAN % /1998' |
'DD-MON-YYYY' |
Error |
'15/JAN/1998' |
'FXDD-MON-YYYY' |
Error |
'15-JAN-1998' |
'FXDD-MON-YYYY' |
Match |
'1-JAN-1998' |
'FXDD-MON-YYYY' |
Error |
'01-JAN-1998' |
'FXDD-MON-YYYY' |
Match |
'1-JAN-1998' |
'FXFMDD-MON-YYYY' |
Match |
Format of Return Values: Examples You can use a format model to specify the format for Oracle to use to return values from the database to you.
The following statement selects the salaries of the employees in Department 80 and uses the TO_CHAR
function to convert these salaries into character values with the format specified by the number format model '$99,990.99
'
SELECT last_name employee, TO_CHAR(salary, '$99,990.99') FROM employees WHERE department_id = 80;
Because of this format model, Oracle returns salaries with leading dollar signs, commas every three digits, and two decimal places.
The following statement selects the date on which each employee from Department 20 was hired and uses the TO_CHAR
function to convert these dates to character strings with the format specified by the date format model 'fmMonth
DD,
YYYY
':
SELECT last_name employee, TO_CHAR(hire_date,'fmMonth DD, YYYY') hiredate FROM employees WHERE department_id = 20;
With this format model, Oracle returns the hire dates without blank padding (as specified by fm
), two digits for the day, and the century included in the year.
Supplying the Correct Format Model: Examples When you insert or update a column value, the datatype of the value that you specify must correspond to the column datatype of the column. You can use format models to specify the format of a value that you are converting from one datatype to another datatype required for a column.
For example, a value that you insert into a DATE
column must be a value of the DATE
datatype or a character string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE
datatype). If the value is in another format, then you must use the TO_DATE
function to convert the value to the DATE
datatype. You must also use a format model to specify the format of the character string.
The following statement updates Hunold's
hire date using the TO_DATE
function with the format mask 'YYYY MM DD' to convert the character string '1998 05 20' to a DATE
value:
UPDATE employees SET hire_date = TO_DATE('1998 05 20','YYYY MM DD') WHERE last_name = 'Hunold';
The following additional formatting rules apply when converting string values to date values (unless you have used the FX
or FXFM
modifiers in the format model to control exact format checking):
You can omit punctuation included in the format string from the date string if all the digits of the numerical format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.
You can omit time fields found at the end of a format string from the date string.
If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-18.
The SYS_XMLGEN
function returns an instance of type XMLType
containing an XML document. Oracle provides the XMLFormat
object, which lets you format the output of the SYS_XMLGEN
function.
Table 2-19 lists and describes the attributes of the XMLFormat
object. The function that implements this type follows the table.
See Also:
|
Table 2-19 Attributes of the XMLFormat Object
Attribute | Datatype | Purpose |
---|---|---|
enclTag |
VARCHAR2(100) |
The name of the enclosing tag for the result of the SYS_XMLGEN function. If the input to the function is a column name, the default is the column name. Otherwise the default is ROW . When schemaType is set to USE_GIVEN_SCHEMA , this attribute also gives the name of the XMLSchema element. |
schemaType |
VARCHAR2(100) |
The type of schema generation for the output document. Valid values are 'NO_SCHEMA ' and 'USE_GIVEN_SCHEMA '. The default is 'NO_SCHEMA '. |
schemaName |
VARCHAR2(4000) |
The name of the target schema Oracle uses if the value of the schemaType is 'USE_GIVEN_SCHEMA '. If you specify schemaName , then Oracle uses the enclosing tag as the element name. |
targetNameSpace |
VARCHAR2(4000) |
The target namespace if the schema is specified (that is, schemaType is GEN_SCHEMA_* , or USE_GIVEN_SCHEMA ) |
dburl |
VARCHAR2(2000) |
The URL to the database to use if WITH_SCHEMA is specified. If this attribute is not specified, then Oracle declares the URL to the types as a relative URL reference. |
processingIns |
VARCHAR2(4000) |
User-provided processing instructions, which are appended to the top of the function output before the element. |
The function that implements the XMLFormat
object follows:
STATIC FUNCTION createFormat( enclTag IN varchar2 := 'ROWSET', schemaType IN varchar2 := 'NO_SCHEMA', schemaName IN varchar2 := null, targetNameSpace IN varchar2 := null, dburlPrefix IN varchar2 := null, processingIns IN varchar2 := null) RETURN XMLGenFormatType deterministic parallel_enable, MEMBER PROCEDURE genSchema (spec IN varchar2), MEMBER PROCEDURE setSchemaName(schemaName IN varchar2), MEMBER PROCEDURE setTargetNameSpace(targetNameSpace IN varchar2), MEMBER PROCEDURE setEnclosingElementName(enclTag IN varchar2), MEMBER PROCEDURE setDbUrlPrefix(prefix IN varchar2), MEMBER PROCEDURE setProcessingIns(pi IN varchar2), CONSTRUCTOR FUNCTION XMLGenFormatType ( enclTag IN varchar2 := 'ROWSET', schemaType IN varchar2 := 'NO_SCHEMA', schemaName IN varchar2 := null, targetNameSpace IN varchar2 := null, dbUrlPrefix IN varchar2 := null, processingIns IN varchar2 := null) RETURN SELF AS RESULT deterministic parallel_enable . . .