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

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

24/03/97

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

March 24, 1997

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

Monday March 24, 1997

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

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