Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF


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

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


Date Values

A valid DATE value must fall between January 1, 1900, and December 31, 9999. It must conform to one of the following three styles, which you can mix throughout a session:

Numeric style -- Specify the day, month, and year as three integers with one or more separators between them, using these rules:

Packed numeric style -- Specify the day, month, and year as three integers with no separators between them, using these rules:

Month name style -- Specify the day and year as integers and the month as text, using these rules:

Valid Dates

To determine whether a text expression (such as an expression with a data type of TEXT or ID) represents a valid DATE value, use the ISDATE program.

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.

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 earlier in this entry. 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 command 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.

Time Dimension Phases

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


Example 9-33 Changing the Date Order

The following commands 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.

DATEVAR = '3 5 1997'

These commands produce the following output.

MAR 5, 1997

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


These commands produce the following output.

MAY 3, 1997