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 3-14 shows the results of the following query for different values of number and 'fmt':

SELECT TO_CHAR(number, 'fmt')
  FROM DUAL;

Datetime Format Elements

A datetime format model is composed of one or more datetime format elements as listed in Table 3-15, "Datetime Format Elements".

• 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.

• The second column indicates whether the format element can be used in the TO_* datetime functions. All format elements can be used in the TO_CHAR function.

• 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 padded with blanks or leading zeroes to a specific length. Refer to the format model modifier FM for more information.

-
/
,
.
;
:
"text"

AD
A.D.

AM
A.M.

BC
B.C.

CC
SCC

D

DAY

DD

DDD

DL

DS

DY

E

EE

FF [1..9]

FM

FX

HH
HH12

HH24

IW

IYY
IY
I

IYYY

J

MI

MM

MON

MONTH

PM
P.M.

Q

RM

RR

RRRR

SS

SSSSS

TS

TZD 

TZH

TZM

TZR

WW

W

X

Y,YYY

YEAR
SYEAR

YYYY
SYYYY

YYY
YY
Y

SELECT TO_CHAR(TO_DATE('0207','MM/YY'), 'MM/YY') FROM DUAL;

TO_CH
-----
02/07
 

SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
                       *
ERROR at line 1:
ORA-01861: literal does not match format string

SELECT TO_CHAR (TO_DATE('02#07','MM/YY'), 'MM/YY') FROM DUAL;

TO_CH
-----
02/07

Datetime Format Elements and Globalization Support

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.

ISO Standard Date Format Elements

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

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.

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

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

Datetime Format Element Suffixes

Table 3-16 lists suffixes that can be added to datetime format elements:

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.

Format Model Examples

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, 2008

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    , 2008 

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 3-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 data type DATE):

UPDATE table 
  SET date_column = TO_DATE(char, 'fmt');

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 data type of the value that you specify must correspond to the column data type of the column. You can use format models to specify the format of a value that you are converting from one data type to another data type required for a column.

For example, a value that you insert into a DATE column must be a value of the DATE data type or a character string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE data type). If the value is in another format, then you must use the TO_DATE function to convert the value to the DATE data type. 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 '2008 05 20' to a DATE value:

UPDATE employees 
  SET hire_date = TO_DATE('2008 05 20','YYYY MM DD') 
  WHERE last_name = 'Hunold';