FISCAL_X_OF_Y

Syntax

Description of the illustration fiscal_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 .

• fmt is a text expression representing a format string. In all cases, the case-sensitivity of the format specifiers follows the same behavior as TO_CHAR. For example, MON specifies an abbreviated month in upper-case (e.g. JAN), mon specifies an abbreviated month name in lower-case (e.g. jan), and Mon specifies an abbreviated month name in title-case (e.g. Jan).

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

• All fiscal functions take an optional fiscal_year_start parameter defining the month and day of the start of the fiscal year.

  If the fiscal_year_start parameter is specified, it’s an expression of type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE representing the first day in the fiscal year. All components other than the month and day are ignored.

  If the fiscal_year_start function argument is not specified, the value of the CALENDAR_FISCAL_YEAR_START system/session parameter is used (which itself defaults to January 1 if not specified). The CALENDAR_FISCAL_YEAR_START system/session parameter is a text value that will be converted to a DATE using the NLS_DATE_FORMAT. An optional second parameter value may be specified to use an explicit format, such as:

ALTER SESSION SET CALENDAR_FISCAL_YEAR_START = '01-JUN-2025', 'DD-MON-YYYY'

• 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