Date Format Models

You can use date format models:

• In the 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.)
• In the 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.

Date Format Elements

A date format model is composed of one or more datetime format elements as listed in Table 2-15.

• 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-15.
• The following datetime format elements can be used in interval and timestamp format models, but not in the original DATE format model: FF, TZD, TZH, TZM, and TZR.

Capitalization of Date Format Elements

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

Punctuation and Character Literals in Date Format Models

You can also 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

-
/
,
.
;
:
"text"

AD
A.D.

AM
A.M.

BC
B.C.

CC
SCC

D

DAY

DD

DDD

DY

E

EE

FF [1..9]

HH

HH12

HH24

IW

IYY
IY
I

IYYY

J

MI

MM

MON

MONTH

PM
P.M.

Q

RM

RR

RRRR

SS

SSSSS

TZD 

TZH

TZM

TZR

WW

W

X

Y,YYY

YEAR
SYEAR

YYYY
SYYYY

YYY
YY
Y

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.

Date Format Elements and Globalization Support

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:

• 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 Oracle9i Database Globalization Support Guide.

The RR Date 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.

RR Date Format Examples

Current year	Date	RR	YY
1995	27-oct-95	1995	1995
1995	27-oct-17	2017	2017
2001	27-oct-17	2017	2017
2001	27-oct-95	1995	2095

Date Format Element Suffixes

Table 2-17 lists suffixes that can be added to datetime format elements:

Table 2-16 Date Format Element Suffixes

Format Model Modifiers

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". 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, because there is no 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 date 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.

Format Modifier 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, 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');

Table 2-17  Matching Character Data and Format Models with the FX Format Model Modifier

String-to-Date Conversion Rules

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

  Table 2-18  Oracle Format Matching

  Original Format Element	Additional Format Elements to Try in Place of the Original
  'MM'	'MON' and 'MONTH'
  'MON	'MONTH'
  'MONTH'	'MON'
  'YY'	'YYYY'
  'RR'	'RRRR'