CALENDAR_X_OF_Y

Syntax

Description of the illustration calendar_x_of_y_fn.gif

• dtexpr is a datetime expression with one of the following types (or any value that can be implicitly converted to DATE): DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE .

• nlsparam is a text expression representing an NLS parameter setting. The only currently supported parameter is NLS_DATE_LANGUAGE, and is specified in the same manner as for TO_CHAR and TO_DATE.

• The index_by parameter is a text value that must resolve to either POSITION or DATE (case-insensitive, defaults to DATE). It is used to distinguish between absolute and position-based numbers for the following cases:

  • CALENDAR_DAY_OF_WEEK

  • FISCAL_DAY_OF_WEEK

  • RETAIL_DAY_OF_WEEK

  • FISCAL_MONTH_OF_YEAR

  • RETAIL_MONTH_OF_YEAR

The CALENDAR_X_OF_Y functions return a NUMBER indicating the relative number of one period with a given ancestor period. Since years have no ancestor, year numbers are obtained with the YEAR_NUMBER functions.