DATE_FORMAT

The DATE_FORMAT command assigns a format template to the definition of an object that has a DATETIME, TIMESTAMP, TIMESTAMP_TZ, TIMESTAMP_LTZ, DSINTERVAL, or YMINTERVAL data type.

The datetime format template is a template that describes the format of datetime data stored in a character string. The template does not change the internal representation of the value in the database. When you convert a character string into a date, the template determines how Oracle OLAP interprets the string.

Note:

You can only use this statement with objects that have a datetime data type that corresponds to a SQL datetime data type.You cannot use this statement for time dimensions that have a DATE-only data type that is unique to the OLAP DML.

To assign a datetime format template to a definition, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

Syntax

DATE_FORMAT [datetime_format_template]

Parameters

datetime_format_template

An expression composed of one or more datetime format elements that specifies the format for entering and displaying the values of the current object. See Table 9-4, "Datetime Format Elements" for the elements that you can specify in the template. Keep the following points in mind when creating a template:

  • The total length of a datetime format template cannot exceed 22 characters

  • 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 datetime format elements cannot be used in the TO_* datetime functions, as noted in Table 2-7, "Datetime Fields and Values".

  • The following datetime format elements can be used in timestamp and interval format models, but not in the original DATETIME format model: FF, TZD, TZH, TZM, and TZR.

  • Many datetime format elements are blank padded to a specific length.

When template is omitted, any existing date format template for the current definition is deleted and the default datetime format template is used. (See "Default Datetime Format Template", for a discussion of the default datetime format template.)

Table 9-4 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.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.

  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

 
D

Yes

Day of week (1-7).

 
DAY

Yes

Name of day, padded with blanks to display width of the widest name of day in the date language used for this element.

 
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 the Oracle Database DATETIME 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 TS element, separated by white space.

 
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 TS element, separated by white space.

 
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 data type or the data type's default precision.

Examples: 'HH:MI:SS.FF'

SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;

 
FM

Yes

Returns a value with no leading or trailing blanks.

See Also: "Format Model Modifiers" in Oracle Database SQL Language Reference

 
FX

Yes

Requires exact matching between the character data and the format model.

See Also: "Format Model Modifiers" in Oracle Database SQL Language 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 display width of the widest name of month in the date language used for this element.

 
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: "The RR Datetime Format Element" in Oracle Database SQL Language Reference

 
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 DL or DS element, separated by white space.

 
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: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).

 
TZH

Yes

Time zone hour. (See TZM format element.)

Example: 'HH:MI:SS.FFTZH:TZM'.

 
TZM

Yes

Time zone minute. (See TZH format element.)

Example: 'HH:MI:SS.FFTZH:TZM'.

 
TZR

Yes

Time zone region information. The value must be a time zone region 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: 'HH:MI:SSXFF'.

 
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.

Usage Notes

Default Datetime Format Template

The default datetime format template is 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.

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.

Datetime Format Element Suffixes

Table 9-5, "Date Format Element Suffixes" lists suffixes that can be added to datetime format elements:

Table 9-5 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

Keep the following in mind when using 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.

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.

See Also:

Oracle Database Reference and Oracle Database Globalization Support Guide for information on globalization support initialization parameters

Uppercase Letters in Date Format Elements

Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the datetime format template 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.

Punctuation and Character Literals in Datetime Format Templates

You can include these characters in a datetime format template:

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

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

Examples

Example 9-75 Changing the Datetime Format Template for an Object

Assume that the default datetime format template is DD_MON_RR as shown in the following statement.

SHOW NLS_DATE_FORMAT
DD-MON-RR

Assume also that you define a variable named mydatetime and assign it the value of CURRENT_TIMESTAMP.

DEFINE mydatetime VARIABLE DATETIME
mydatetime = CURRENT_TIMESTAMP

When you report on value of mydatetime, the following value is displayed. This value has the format determined by the setting NLS_DATETIME FORMAT. It shows only day, month, and year values in the order specified by

REPORT mydatetime

MYDATETIME
-----------
02-FEB-07

Now you change the date format map for mydatetime by issuing the following statements.

CONSIDER mydatetime
DATE_FORMAT MON-RRRR-DD-HH24

A display of the value of mydatetime, now includes hour as a 24-hour value.

REPORT mydatetime

MYDATETIME
--------------
FEB-2007-02-10