Many organizations do not run their books on the calendar year. India and the United Kingdom use a year that starts on 1 April, the US federal government one that starts on 1 October, and many companies their own date. The FISCAL_ functions of Oracle AI Database 26ai name and compute periods of such a fiscal year: its year and quarter labels, its start and end dates, and the position of a date within it.
Code for This Guide
The main example is in the examples/date-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
fiscal_year(dtexpr [, fiscal_year_start [, fmt [, nlsparam]]]) -- and FISCAL_QUARTER, FISCAL_MONTH, FISCAL_WEEK, FISCAL_DAY, -- FISCAL_..._OF_..., FISCAL_..._START_DATE, FISCAL_..._END_DATE, FISCAL_ADD_...
The family has the same members as the CALENDAR_ functions, with FISCAL_ in place of CALENDAR_. fiscal_year_start is a date whose day and month mark the start of the fiscal year; without it, the CALENDAR_FISCAL_YEAR_START parameter applies.
A Fiscal Year Starting 1 April
The first query places 15 March 2026 in a fiscal year that starts on 1 April. The second shows retail periods, covered separately.
Example:
-- a fiscal year that starts on 1 April
select fiscal_year(d, date '2026-04-01') as fiscal_year,
fiscal_quarter(d, date '2026-04-01') as fiscal_quarter,
fiscal_month_of_year(d, date '2026-04-01') as fiscal_month_no,
fiscal_year_start_date(d, date '2026-04-01') as fy_start
from (select date '2026-03-15' as d);
select retail_year(d) as retail_year, retail_month(d) as retail_month,
retail_week(d) as retail_week, retail_month_start_date(d) as month_start,
retail_month_end_date(d) as month_end
from (select date '2026-03-15' as d);Output:
FISCAL_YEAR FISCAL_QUARTER FISCAL_MONTH_NO FY_START ______________ _________________ __________________ ______________ FY2026 Q4-FY2026 3 01-APR-2025 RETAIL_YEAR RETAIL_MONTH RETAIL_WEEK MONTH_START MONTH_END ______________ _______________ ______________ ______________ ______________ RY2026 MAR-RY2026 W07-RY2026 01-MAR-2026 04-APR-2026
15 March 2026 falls in the fourth quarter of fiscal year 2026, which started on 1 April 2025, and March is the third month of that quarter. The fiscal year is named after the calendar year in which it ends.
A Fiscal Year Starting 1 October
With a 1 October start, the new fiscal year begins on 1 October 2025, so 30 September and 1 October fall in different years.
Example:
-- the same dates in a fiscal year starting 1 October (as in the US federal government)
select d, fiscal_year(d, date '2025-10-01') as fy, fiscal_quarter(d, date '2025-10-01') as fq,
fiscal_quarter_start_date(d, date '2025-10-01') as fq_start
from (values (date '2025-09-30'), (date '2025-10-01'), (date '2026-03-15')) t (d);Output:
D FY FQ FQ_START ______________ _________ ____________ ______________ 30-SEP-2025 FY2025 Q4-FY2025 01-JUL-2025 01-OCT-2025 FY2026 Q1-FY2026 01-OCT-2025 15-MAR-2026 FY2026 Q2-FY2026 01-JAN-2026
15 March 2026 is in the second fiscal quarter, which started on 1 January 2026. Only the day and month of fiscal_year_start matter; the year in it does not change the result.
Things to Know
- Pass the start date in every call, or set the parameter once, so all queries agree on the fiscal year.
- The labels such as FY2026 and Q4-FY2026 ignore NLS_DATE_FORMAT; a format argument changes them.
- Group by fiscal_quarter(d, start) to report by fiscal quarter, and order by fiscal_quarter_start_date(d, start) to sort the groups.
Related Guides
Conclusion
The FISCAL_ functions mirror the CALENDAR_ functions for a year that starts on any day you choose. Give them the fiscal year start, and they return fiscal year and quarter labels, period start and end dates, and positions within the fiscal year.
