Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Basic Elements of Oracle SQL, 4 of 10
A format model is a character literal that describes the format of DATE
or NUMBER
data stored in a character string. You can use a format model as an argument of the TO_CHAR
and TO_DATE
functions:
See "TO_CHAR (date conversion)", "TO_CHAR (number conversion)", and "TO_DATE". Note that a format model does not change the internal representation of the value in the database.
This section describes how to use:
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 commission values of the employees in Department 30 and uses the TO_CHAR
function to convert these commissions into character values with the format specified by the number format model '$9,990.99
':
SELECT ename employee, TO_CHAR(comm, '$9,990.99') commission FROM emp WHERE deptno = 30; EMPLOYEE COMMISSION ---------- ---------- ALLEN $300.00 WARD $500.00 MARTIN $1,400.00 BLAKE TURNER $0.00 JAMES
Because of this format model, Oracle returns commissions with leading dollar signs, commas every three digits, and two decimal places. Note that TO_CHAR
returns null for all employees with null in the COMM
column.
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 ename, TO_CHAR(Hiredate,'fmMonth DD, YYYY') hiredate FROM emp WHERE deptno = 20; ENAME HIREDATE ---------- ------------------ SMITH December 17, 1980 JONES April 2, 1981 SCOTT April 19, 1987 ADAMS May 23, 1987 FORD December 3, 1981 LEWIS October 23, 1997
With this format model, Oracle returns the hire dates (as specified by "fm" and discussed in "Format Model Modifiers") without blank padding, two digits for the day, and the century included in the year.
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. When you insert or update a column value, the datatype of the value that you specify must correspond to the column's datatype. 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, 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 BAKER'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 emp SET hiredate = TO_DATE('1998 05 20','YYYY MM DD') WHERE ename = 'BLAKE';
You can use number format models:
TO_CHAR
function to translate a value of NUMBER
datatype to VARCHAR2
datatype
TO_NUMBER
function to translate a value of CHAR
or VARCHAR2
datatype to NUMBER
datatype
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, 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-7 lists the elements of a number format model. Examples are shown in Table 2-8.
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 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
. For information on these parameters, see Oracle8i Reference and Oracle8i National Language Support Guide.
You can change the default date format for your session with the ALTER
SESSION
statement. For information on changing the settings of these parameters, see "ALTER SESSION".
Table 2-8 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_CHAR
function to translate a DATE
value that is in a format other than the default date format
TO_DATE
function to translate a character value that is in a format other than the default date format
The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT
or implicitly with the initialization parameter NLS_TERRITORY
. For information on these parameters, see Oracle8i Reference.
You can change the default date format for your session with the ALTER
SESSION
statement. For information, see "ALTER SESSION".
The total length of a date format model cannot exceed 22 characters.
A date format model is composed of one or more date format elements as listed in Table 2-9.
TO_DATE
function, as noted in Table 2-9.
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.
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 date format elements depends on the country and language in which you are using Oracle. For example, these date 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 date format elements are always in English.
The date 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
.
For information on national language support initialization parameters, see Oracle8i Reference and Oracle8i National Language Support Guide.
Oracle calculates the values returned by the date 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 date format elements YYYY, YYY, YY, Y, and WW, see the discussion of national language support in Oracle8i National Language Support Guide.
The RR date format element is similar to the YY date format element, but it provides additional flexibility for storing date values in other centuries. The RR date format element allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year. It will also allow you to store 20th century dates in the 21st century in the same way if necessary.
If you use the TO_DATE function with the YY date format element, the date value returned is always in the current century. If you use the RR date format element instead, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Table 2-10 summarizes the behavior of the RR date format element.
The following examples demonstrate the behavior of the RR date 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
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 date format element allows you to write SQL statements that will return the same values after the turn of the century.
Table 2-11 lists suffixes that can be added to date format elements:
Suffix | Meaning | Example Element | Example Value |
---|---|---|---|
TH |
Ordinal Number |
DDTH |
4TH |
SP |
Spelled Number |
DDSP |
FOUR |
SPTH or THSP |
Spelled, ordinal number |
DDSPTH |
FOURTH |
Restrictions: |
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, 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
Note that the statement above also uses the FM modifier. If FM is omitted, 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-12 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):
Table 2-13 Oracle Format Matching
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' |
'MON' and 'MONTH' |
|
|
|
|
|
|
|
|
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|