Fiscal Calendar Options

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Fiscal calendar options configure standard or custom fiscal years in a query. A fiscal calendar can differ from the standard calendar to match your business needs.

You can use fiscal calendar options in the EXTRACT and date_part, DATE_TRUNC, DATEDIFF, DATE_ADD, and TO_CHAR functions when the field is a fiscal date unit:

1fiscal_week
2fiscal_month
3fiscal_quarter
4fiscal_year

• fiscal_year_start_month => <value>: The first month of your fiscal year. Default: 1 (January). For example, if the fiscal year starts in February, <value> is 2.

• first_day_of_fiscal_week => <value>: The first day of your fiscal week. Default: 1 (Monday). For example, if the fiscal year starts in January and <value> is 1 (Monday), fiscal_week for the date 2023-01-02 returns 2, because 2023-01-01 is a Sunday, making 2023-01-02 the second week of the fiscal year.

• use_start_date_as_fiscal_year_name => <value>: Sets the naming of your fiscal year to the current year or the next year. Default: true. For example, the fiscal year starting at 2020-04-01 is 2020 if <value> is true. If <value> is false, the fiscal year is 2021.

• fiscal_period_table => TABLE(<table_expression>): Uses a custom fiscal calendar that you define in a <table_expression>. The <table_expression> is a lookup table that maps each day to its fiscal periods. It has these columns:

1DayDate__c                      date    -- The calendar date (lookup key for the table)
2YearNumber__c                   int     -- The fiscal year number (for example, 2025)
3QuarterNumber__c                int     -- Fiscal quarter within the year (1-4)
4MonthNumber__c                  int     -- Fiscal month within the year (for example, 1-12 or 1-13)
5WeekNumber__c                   int     -- Fiscal week within the year
6FiscalYearFirstDayDate__c       date    -- First day of the fiscal year
7FiscalQuarterFirstDayDate__c    date    -- First day of the fiscal quarter
8FiscalMonthFirstDayDate__c      date    -- First day of the fiscal month
9FiscalWeekFirstDayDate__c       date    -- First day of the fiscal week
10RunningYearNumber__c            int     -- Absolute year index (1, 2, 3, ...)
11RunningQuarterNumber__c         int     -- Absolute quarter index (1, 2, 3, ...)
12RunningMonthNumber__c           int     -- Absolute month index (1, 2, 3, ...)
13RunningWeekNumber__c            int     -- Absolute week index (1, 2, 3, ...)

• You can’t combine standard and custom fiscal calendar options.
• In Data 360, functions use the fiscal calendar configured in your org by default. If you haven’t configured a calendar, the function uses a standard fiscal calendar with the default values.

1SELECT EXTRACT(fiscal_week FROM timestamp '2000-01-02 20:38:40', first_day_of_fiscal_week => 7);

Returns 2.

1SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', fiscal_year_start_month => 3);

Returns 1999.

1SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', use_start_date_as_fiscal_year_name => false);

Returns 2001.

1SELECT DATEDIFF('fiscal_year', date '2025-02-01', date '2025-03-01', fiscal_year_start_month => 3);

Returns 1.

• EXTRACT and date_part
• DATE_TRUNC
• DATEDIFF
• DATE_ADD
• TO_CHAR
• Localized Week Options
• Date/Time Functions and Operators