Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

DATEFORMAT

The DATEFORMAT option holds the template used for displaying DATE-only data type values and converting DATE-only values to text values. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). It can also include additional text.

Data Type

TEXT

Syntax

DATEFORMAT = template

Parameters

template

A TEXT expression that specifies the template for displaying dates. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components. The default template is '<DD><MTXT><YY>'.

Table 5-2, "DATEFORMAT Templates for Day", Table 5-3, "DATEFORMAT Templates for Week", Table 5-4, "DATEFORMAT Templates for Month", and Table 5-5, "DATEFORMAT Templates for Year" present the valid formats for each component. The tables provide two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-2 DATEFORMAT Templates for Day

Format Meaning March 1, 1990 November 12, 2051

<D>

One digit or two digits

1

12

<DD>

Two digits

01

12

<DS>

Space-padded, two digits

1

12

<DT>

Ordinal, uppercase

1ST

12TH

<DTL>

Ordinal, lowercase

1st

12th


Table 5-3, "DATEFORMAT Templates for Week" presents the valid formats for weeks. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-3 DATEFORMAT Templates for Week

Format Meaning March 1, 1990 November 12, 2051

<W>

Numeric

4

1

<WT>

First letter, uppercase

W

S

<WTXT>

First three letters, uppercase.

WED

SUN

<WTXTL>

First three letters, lowercase

Wed

Sun

<WTEXT>

Full name, uppercase

WEDNESDAY

SUNDAY

<WTEXTL>

Full name, lowercase

Wednesday

Sunday


Note that when you specify a format of <WTXT>, <WTXTL>, <WTEXT>, or <WTEXTL>, the case in which the value is specified in DAYNAMES affects the displayed value:

  • When the name in DAYNAMES is entered as all lowercase, the entire name is converted to uppercase. Otherwise, the first letter is converted to uppercase and the second and subsequent letters remain in their original case.

  • When the name in DAYNAMES is entered as all uppercase, the second and subsequent letters are converted to lowercase. Otherwise, the entire name remains in the case specified in DAYNAMES.

Table 5-4, "DATEFORMAT Templates for Month" presents the valid formats for months. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-4 DATEFORMAT Templates for Month

Format Meaning March 1, 1990 November 12, 2051

<M>

One digit or two digits

1

11

<MM>

Two digits

03

11

<MS>

Space-padded, two digits

3

11

<MT>

First letter, uppercase

M

N

<MTXT>

First three letters, uppercase

MAR

NOV

<MTXTL>

First three letters, lowercase

Mar

Nov


Note that when you specify a format of <MTXT> or <MTXTL>, the case in which the value is specified in MONTHNAMES affects the displayed value:

  • When the name in MONTHNAMES is entered as all lowercase, the entire name is converted to uppercase. Otherwise, the first letter is converted to uppercase and the second and subsequent letters remain in their original case.

  • When the name in MONTHNAMES is entered as all uppercase, the second and subsequent letters are converted to lowercase. Otherwise, the entire name remains in the case specified in MONTHNAMES.

Table 5-5, "DATEFORMAT Templates for Year" presents the valid formats for years. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-5 DATEFORMAT Templates for Year

Format Meaning March 1, 1990 November 12, 2051

<YY>

Two digits or four digits

90

2051

<YYYY>

Four digits

1990

2051


Usage Notes

Specifying Angle Brackets as Text in a DATEFORMAT Template

To include an angle bracket as additional text in a template, specify two angle brackets for each angle bracket to be included as text (for example, to display the entire date in angle brackets, specify '<<<D><M><YY>>>').

Month and Day Names

The names used in the month component for the MT, MTXT, MTXTL, MTEXT, and MTEXTL formats are drawn from the current setting of the MONTHNAMES option. The names used in the day-of-the-week component for the WT, WTXT, WTXTL, WTEXT, and WTEXTL formats are drawn from the current setting of the DAYNAMES option.

Specifying Abbreviations for Day and Month

You can set the DAYABBRLEN and MONTHABBRLEN options to use abbreviations of different lengths for day and month names.

Out-of-Range Years for DATEFORMAT

When you specify the YY format, and a year outside the range of 1950 to 2049 is to be displayed, the year is displayed in four digits.

Automatic Conversion of DATE-only Values to Text Values

When you use a value with DATE-only data type where a text data type is expected. Oracle OLAP also uses the date template in the DATEFORMAT option to automatically convert the date to a text value. When you want to override the current DATEFORMAT template, you can convert the date result to text by using the CONVERT function with a date-format argument.

Once a DATE-only value is stored in a text variable, the DATEFORMAT template is no longer used to format the display of the value, and subsequent changes to DATEFORMAT have no impact.

DATE-only Dimension Values

The DATEFORMAT option does not how Oracle OLAP displays DATE-only values of DAY, WEEK, MONTH, QUARTER, and YEAR dimensions. How these values are displayed is controlled by a VNF (value name format) attached to the dimension definition, or by default conventions for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions as described in Table 2-5, "Default VNFs for DWMQY Dimensions".

Examples

Example 5-13 Changing the Format of Dates

The following statements define a DATE-only variable and set its value to March 24, 1997, then set the date format to two digits each in the order of day, month, and year, and send the result to the current outfile.

DEFINE datevar VARIABLE DATE
datevar = '24Mar97'
DATEFORMAT = '<DD>/<MM>/<YY>'
SHOW datevar

These statements produce the following output.

24/03/97

The following statements change the date format to month (text), day (two digits), and year (four digits), and send the result to the current outfile.

DATEFORMAT = '<MTEXTL> <D>, <YYYY>'
SHOW DATEVAR

These statements produce the following output.

March 24, 1997

The following statements change the date format to day of the week (text), month (text), day (one or two digits), and year (four digits), and send the result to the current outfile.

DATEFORMAT = '<WTEXTL> <MTEXTL> <D>, <YYYY>'
SHOW DATEVAR

These statements produce the following output.

Monday March 24, 1997

Example 5-14 Including Text in the Format of a Date

The following statements save and then change the DATEFORMAT option to include extra text for an analytic workspace startup greeting.

PUSH DATEFORMAT
DATEFORMAT = 'Hello.  Today is <wtextl>, the <dtl> -
OF <MTEXTL>.'
SHOW TODAY
POP DATEFORMAT

When today's date is May 30, 1997, the following output is sent to the current outfile when the program is run.

Hello.  Today is Friday, the 30th of May.