FISCAL_ADD_X_PERIODS

Syntax


Description of fiscal_add_x_periods.eps follows
Description of the illustration fiscal_add_x_periods.eps

Purpose

The fiscal variants of this class of functions is equivalent to the definitions above for calendar. The difference has to do with what constitutes a month. If the fiscal year start is on the 1st of a month, these functions will return exactly the same value as their calendar counterparts. If the fiscal year start is not on the 1st, however, then they will diverge.

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

  • The num_periods parameter is an integer that when positive will add periods and when negative will subtract periods.

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

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

FISCAL_ADD_YEARS adds or subtracts the given number of years, where a year is always defined as a period of 12 months. See below for how months are handled.

FISCAL_ADD_QUARTERS adds or subtracts the given number of quarters, where a quarter is always defined as a period of 3 months. See below for how months are handled.

FISCAL_ADD_MONTHS This is handled the same as CALENDAR_ADD_MONTHS. When the fiscal year start is not on the first of the month, the positional day within the given month is mapped to the corresponding positional day within the target month. The same rules apply when the starting positional day is the last day of the starting fiscal month, or when the starting positional day is greater than the number of days in the target fiscal month.

FISCAL_ADD_WEEKS adds or subtracts the given number of weeks, where a week is always defined as a period of 7 days.

FISCAL_ADD_DAYS simply adds or subtracts the given number of days.