The DATEORDER option holds three characters that indicate the intended order of the month, day, and year components of the DATE-only values in an analytic workspace for those cases in which their interpretation is ambiguous. Oracle OLAP automatically refers to DATEORDER whenever you enter an ambiguous DATE-only value or convert one from a text value. For information about date values, see "Date-only Data Type".
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'.
When you enter an unambiguous DATE-only 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-only Conversion
When you use a text value where a DATE-only value is expected, or when you store a text value in a DATE-only variable, the text value must conform to a style listed "Date-only Input Values". Oracle OLAP automatically converts the text value to a DATE-only 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-only value, use the CONVERT function with the date-order argument.
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-only 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 must 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.
The DATEORDER option is used to interpret a phase argument to a DEFINE DIMENSION statement for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions.
Example 5-15 Changing the Date Order
The following statements define and assign a value to a DATE-only 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-only variable is interpreted.
DATEORDER = 'DMY' SHOW DATEVAR
These statements produce the following output.
MAY 3, 1997