Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
A format model is a character literal that describes the format of DATE
or NUMBER
data stored in a character string. When you convert a character string into a date or number, a format model tells Oracle how to interpret the string. In SQL statements, you can use a format model as an argument of the TO_CHAR
and TO_DATE
functions:
For example,
17:45:29
' is 'HH24:MI:SS
'.11-Nov-1999
' is 'DD-Mon-YYYY
'.$2,304.25
' is '$9,999.99
'.For lists of date and number format model elements, see Table 2-13, " Number Format Elements" and Table 2-15, " Datetime Format Elements".
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:
|
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 '$9,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 (as specified by "fm
") without blank padding, two digits for the day, and the century included in the year.
See Also:
"Format Model Modifiers" for a description of the |
When you insert or update a column value, the datatype of the value that you specify must correspond to the column's datatype. 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';
This remainder of this section describes how to use:
You can use number format models:
TO_CHAR
function to translate a value of NUMBER
datatype to VARCHAR2
datatypeTO_NUMBER
function to translate a value of CHAR
or VARCHAR2
datatype to NUMBER
datatypeAll 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. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~). This event typically occurs when you are using TO_CHAR
with a restrictive number format string, causing a rounding operation.
A number format model is composed of one or more number format elements. Table 2-13 lists the elements of a number format model. Examples are shown in Table 2-14.
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-14 shows the results of the following query for different values of number
and 'fmt'
:
SELECT TO_CHAR(number, 'fmt') FROM DUAL;
You can use date format models:
TO_*
datetime function to translate a character value that is in a format other than the default date format into a DATE
value. (The TO_
* datetime functions are TO_CHAR
, TO_DATE
, TO_TIMESTAMP
, TO_TIMESTAMP_TZ
, TO_YMINTERVAL
, and TO_DSINTERVAL
.)TO_CHAR
function to translate a DATE
value that is in a format other than the default date format into a string (for example, to print the date from an application)The total length of a date format model cannot exceed 22 characters.
The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT
or implicitly with the initialization parameter NLS_TERRITORY
. You can change the default date format for your session with the ALTER
SESSION
statement.
See Also:
|
A date format model is composed of one or more datetime format elements as listed in Table 2-15.
TO_*
datetime functions, as noted in Table 2-15.DATE
format model: FF
, TZD
, TZH,
TZM
, and TZR
.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 also include these characters in a date format model:
These characters appear in the return value in the same location as they appear in the format model.
Element |
Specify in TO_* datetime functions?a |
Meaning |
---|---|---|
- / , . ; : "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). |
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). |
|
Yes |
Fractional seconds; no radix character is printed (use the
|
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; JAN = 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; JAN-MAR = 1). |
RM |
Yes |
Roman numeral month (I-XII; JAN = I). |
RR |
Yes |
Lets you store 20th century dates in the 21st century using only two digits. See "The RR Date Format Element" for detailed information. |
RRRR |
Yes |
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4-digit year. |
SS |
Yes |
Second (0-59). |
SSSSS |
Yes |
Seconds past midnight (0-86399). |
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 Example: |
TZM |
Yes |
Time zone minute. (See 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 |
Example: |
Y,YYY |
Yes |
Year with comma in this position. |
YEAR SYEAR |
No |
Year, spelled out; "S" prefixes BC dates with "-". |
YYYY SYYYY |
Yes |
4-digit year; "S" prefixes BC dates with "-". |
YYY YY Y |
Yes |
Last 3, 2, or 1 digit(s) of year. |
a The |
Oracle returns an error if an alphanumeric character is found in the date string where punctuation character is found in the format string. For example:
TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')
returns an error.
The functionality of some datetime format elements depends on the country and language in which you are using Oracle. For example, these datetime format elements return spelled values:
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:
Oracle9i Database Reference and Oracle9i 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 Oracle9i 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:
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-17 lists suffixes that can be added to datetime format elements:
Suffix | Meaning | Example Element | Example Value |
---|---|---|---|
TH |
Ordinal Number |
|
|
SP |
Spelled Number |
|
|
SPTH or THSP |
Spelled, ordinal number |
|
|
Notes: |
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.
"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR
function:
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, because there is no blank padding, the length of the return value may vary.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."Format exact". This modifier specifies exact matching for the character argument and date format model of a TO_DATE
function:
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-18 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');
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):
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' |
'MON' and 'MONTH' |
|
|
|
|
|
|
|
|
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-20 lists and describes the attributes of the XMLFormat
object. The function that implements this type follows the table.
See Also:
|
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, 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