FISCAL

Syntax

Description of the illustration fiscal_fn.gif

Purpose

The fiscal functions return a VARCHAR2 value that uniquely identifies a member in the specified level within the standard Gregorian calendar offset by the fiscal start day.

The fiscal functions return a VARCHAR2 value that uniquely identifies a member in an implicit hierarchy at a particular level. For this reason, the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT do not apply to these functions. They each have their own default formats. These functions respect the setting of NLS_DATE_LANGUAGE.

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

Format for Fiscal Functions

The following table describes the format strings for each of the functions. The required formats must appear somewhere in the format string, and an error will be raised if not present. The optional formats may appear in the format string. All other format specifiers are prohibited and an error will be raised if included. The default format strings are similar to the ones for the calendar hierarchies, with years prefixed with FY.

Table 6 Format for Fiscal Functions

Function	Required Formats	Optional Formats	Default Format String
FISCAL_YEAR	YYYYor SYYYY	None	"FY"SYYYY Example: FY2024
FISCAL_QUARTER	YYYYor SYYYY Q	None	"Q"Q-"FY"SYYYY Example: Q1-FY2024
FISCAL_MONTH	YYYYor SYYYY One of MM, mon, MONTH	Q	MON-"FY"SYYYY Example: JAN-FY2024
FISCAL_WEEK	YYYYor SYYYY WW	None	"W"WW-"FY"SYYYY Example: W01-FY2024
FISCAL_DAY	YYYYor SYYYY WW and one of and one of : DY DAY DD and one of MM MON MONTH DDD	Q	DD-MON-"FY"SYYYY Example: 01-FYJAN-2024

Format Descriptions for Fiscal Functions

The following table describes the interpretation of the format specifiers for fiscal hierarchies. Note that all numeric formats are relative to the start of the fiscal year, and the fiscal year and month are determined by the last calendar day of the associated fiscal year or month respectively. The capitalization rules for MON, MONTH, DY, and DAY all follow the usage elsewhere (i.e. TO_CHAR) with regard to lower-case (e.g. month => june), upper-case (e.g. MONTH => JUNE), and title-case (e.g. Month => June).

Table 7 Format Descriptions for Fiscal Functions

Format Specifier	Description
YYYY	4-digit calendar year of the last day in the fiscal year (only positive years supported). For example, if the fiscal year starts on June 1, then June 1, 2024 would be in year 2025.
SYYYY	4-digit calendar year of the last day in the fiscal year, with BC years as negative.
Q	Quarter of fiscal year (1-4), with quarter 1 representing the start of the fiscal year. For example, if the fiscal year starts on June 1, then June 1 would be in quarter 1.
MM	Month of year (1-12), 2-digit, representing the associated calendar month of the last day in the fiscal month. For example, if the fiscal year starts on June 15, then June 20 would be in month 07.
MON	Abbreviated name of month, where the month name is determined by the calendar month of the last day in the fiscal month. For example, if the fiscal year starts on June 15, then June 20 would be in month JUL.
MONTH	Name of month, where the month name is determined by the calendar month of the last day in the fiscal month. For example, if the fiscal year starts on June 15, then June 20 would be in month JULY.
WW	Week number (1-53), 2-digits with week 1 representing the start of the fiscal year. For example, if the fiscal year starts on June 1, then June 1 would be in week 1.
DY	Abbreviated name of day
DAY	Name of day
D	Day of week (1-7). Depends on the NLS_TERRITORY
DD	Day of month (1-31), 2-digits, with day 1 representing the first day of the fiscal month. For example, if the fiscal year starts on June 15, then June 20 would be day 6 (in the month of July).
DDD	Day of year (1-366), 3-digits, with day 1 representing the first day in the fiscal year.