DATEORDER

The DATEORDER option holds three characters that indicate the intended order of the month, day, and year components of the DATE values in a workspace for those cases in which their interpretation is ambiguous. Oracle OLAP automatically refers to DATEORDER whenever you enter an ambiguous DATE value or convert one from a text value. For information about date values, see "Date Data Types".

Data type

Syntax

DATEORDER = order

Arguments

order: One of the following text expressions: 'MDY', 'DMY', 'YMD', 'YDM', 'MYD', 'DYM'. Each letter represents a component of the date. M stands for the month, D for the day, and Y for the year. The default date order is 'MDY'.

Notes

Ambiguous Dates

When you enter an unambiguous DATE value or convert a text value that has only one interpretation as a date, it is handled without consulting the DATEORDER option. For example, in 03-24-97 the 97 can only refer to the year. Considering what is left, the 24 cannot refer to the month, so it must be the day. Only 03 is left, so it must be the month. When, however, the interpretation is ambiguous, as in the value 3-5-97, the current value of DATEORDER is used to interpret the meaning of each component.

DATEORDER and TEXT-to-DATE Conversion

When you use a text value where a DATE value is expected, or when you store a text value in a DATE variable, the text value must conform to one of the styles listed "Valid DATE Values". Oracle OLAP automatically converts the text value to a DATE value. When the meaning of the text value is ambiguous, the current setting of DATEORDER is used to interpret the value.

To override the current DATEORDER setting in converting a text value to a DATE value, use the CONVERT function with the date-order argument.

Essential Date Components

Suppose you want to assign a date value to a DAY, WEEK, MONTH, QUARTER, or YEAR dimension using a MAINTAIN statement or to a valueset using the LIMIT command. When you specify the value in the form of a DATE expression or a text literal, Oracle OLAP uses the DATEORDER option to interpret the value. When supplying a text literal, you can use any valid input style for dates. However, you need to supply only the date components that are necessary for identifying a time period in the particular type of dimension or valueset you are using. For example, for a MONTH dimension or its valueset, you can specify a complete date, such as 30jun97, or you can provide only the essential components, such as jun97 or 0697.

DWMQY Dimension Phases

The DATEORDER option is used to interpret a phase argument to a DEFINE DIMENSION statement for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions.

Examples

Example 11-35 Changing the Date Order

The following statements define and assign a value to a DATE variable, specify the date format and the date order, and send the output to the current outfile.

DEFINE datevar VARIABLE DATE
dATEFORMAT = '<MTXT> <D>, <YYYY>'
DATEORDER = 'MDY'
DATEVAR = '3 5 1997'
SHOW DATEVAR

These statements produce the following output.

MAR 5, 1997

The following statements change the date order, and, therefore, the way the same value of the DATE variable is interpreted.

DATEORDER = 'DMY'
SHOW DATEVAR

These statements produce the following output.

MAY 3, 1997