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

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

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:

The day and month components can have one digit or two digits.
For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).
To separate the components, you can use a space (), dash (-), slash (/), colon (:), or comma (,).
Examples: '24/4/97' or '24-04-1997'

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

The day and month components must have two digits. When the day or month is less than 10, it must be preceded by a zero.
For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).
You cannot use any separators between the date components.
Examples: '240497' or '04241997'

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

The month component must match one of the names listed in the MONTHNAMES option. You can abbreviate the month name to one letter or more, when you supply enough letters to uniquely match the beginning of a name in MONTHNAMES. The case of the letters in the month component (uppercase or lowercase) does not need to match the case in MONTHNAMES.
The day component can have one digit or two digits.
For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).
When the day and year components are adjacent, they must have at least one separator between them. As separators, you can use a space (), dash (-), slash (/), colon (:), or comma (,). When you want, you can place one or more separators between the day and month or between the year and month.
Examples: '24APR97' or '24 ap 97' or 'April 24, 1997'

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.

Examples

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.

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

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.

DATEORDER = 'DMY'
SHOW DATEVAR

These commands produce the following output.

MAY 3, 1997