Oracle Database 26ai Calendar Functions

Very often analytical queries have a requirement to group as well as filter data based on calendar periods like a fiscal year or custom financial period.

Oracle Database 26ai has introduced a number of new Calendar Functions like CALENDAR_YEAR,CALENDAR_QUARTER,FISCAL_YEAR,FISCAL_MONTH, FISCAL_YEAR_END_DATE etc which enables us to extract time-based information across different calendar hierarchies directly from date columns in tables.

The Calendar Functions support not only standard Gregorian calendar, but also calendars based on fiscal and retail hierarchies.

Extract the Year

SQL> SELECT CALENDAR_YEAR(DATE '2026-01-26', 'YYYY') YEAR FROM DUAL;

YEAR
_______
2026

Extract the Quarter number

SQL> SELECT CALENDAR_QUARTER(DATE '2026-01-26', 'Q-YYYY') YEAR FROM DUAL;

YEAR
_________
1-2026

SQL> SELECT CALENDAR_QUARTER(DATE '2026-12-26', 'Q-YYYY') YEAR FROM DUAL;

YEAR
------
4-2026

Extract the Week number

SQL> SELECT CALENDAR_WEEK(DATE '2026-01-26', 'WW-YYYY') YEAR FROM DUAL;

YEAR
__________
04-2026


SQL> SELECT CALENDAR_WEEK(DATE '2026-02-26', 'WW-YYYY') YEAR FROM DUAL;

YEAR
-------
09-2026

Extract the Month

SQL> SELECT CALENDAR_MONTH(DATE '2026-01-26', 'MON-YYYY') YEAR FROM DUAL;

YEAR
___________
JAN-2026

SQL>  SELECT CALENDAR_MONTH(DATE '2026-02-26', 'MON-YYYY') YEAR FROM DUAL;

YEAR
--------
FEB-2026

Extract the Financial Year for a given date based on the Financial Calendar (which ends on 30th June and starts on 1st July)

SQL>  SELECT FISCAL_YEAR(DATE '2026-01-27',  DATE '2026-07-01') from dual;

FISCAL_YEAR(DATE '2026-01-27',  DATE '2026-07-01')
_____________________________________________________
FY2026

SQL>  SELECT FISCAL_YEAR(DATE '2026-07-27',  DATE '2026-07-01') from dual;

FISCAL_YEAR(DATE '2026-07-27',  DATE '2026-07-01')
_____________________________________________________
FY2027

Extract the month as well as Financial Year the given date pertains to

SQL> SELECT FISCAL_MONTH(DATE '2026-01-27',  DATE '2026-07-01')  FROM DUAL;

FISCAL_MONTH(DATE '2026-01-27',  DATE '2026-07-01')
______________________________________________________
JAN-FY2026

SQL>  SELECT FISCAL_MONTH(DATE '2026-07-27',  DATE '2026-07-01') FROM DUAL;

FISCAL_MONTH(DATE '2026-07-27',  DATE '2026-07-01')
______________________________________________________
JUL-FY2027

Extract the last date of the current Financial Year

SQL> SELECT FISCAL_YEAR_END_DATE (DATE '2026-01-27',  DATE '2026-07-01') FROM DUAL;

FISCAL_YEAR_END_DATE (DATE '2026-01-27',  DATE '2026-07-01')
_______________________________________________________________
30-JUN-26

Extract the date on which the current Financial Year started

SQL> SELECT FISCAL_YEAR_START_DATE (DATE '2026-01-27',  DATE '2026-07-01') FROM DUAL;

FISCAL_YEAR_START_DATE (DATE '2026-01-27',  DATE '2026-07-01')
_________________________________________________________________
01-JUL-25