Reports constantly need the name of a period (Q1-2026, MAR-2026, week 11), its first and last day, or a date a few periods later. Oracle AI Database 26ai adds a family of CALENDAR_ functions that answer these directly for the Gregorian calendar, so you no longer build them from TRUNC, TO_CHAR, ADD_MONTHS, and LAST_DAY.
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. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
The Family
Syntax:
calendar_year(dtexpr [, fmt [, nlsparam]]) -- and the other members below
| Functions | Return |
|---|---|
| CALENDAR_YEAR, _QUARTER, _MONTH, _WEEK, _DAY | The name of the period a date is in, such as Q1-2026 |
| CALENDAR_YEAR_NUMBER | The year as a number |
| CALENDAR_DAY_OF_WEEK, _OF_MONTH, _OF_QUARTER, _OF_YEAR | The day's number within a period |
| CALENDAR_WEEK_OF_YEAR, CALENDAR_MONTH_OF_QUARTER, _OF_YEAR, CALENDAR_QUARTER_OF_YEAR | A period's number within a larger period |
| CALENDAR_YEAR_START_DATE, _QUARTER_, _MONTH_, _WEEK_START_DATE, and the _END_DATE versions | The first or last day of a period |
| CALENDAR_ADD_YEARS, _QUARTERS, _MONTHS, _WEEKS, _DAYS | The date moved by a number of periods |
| CALENDAR_SINCE | The time since a date in words |
The period names have their own formats and ignore NLS_DATE_FORMAT; an optional format argument changes them.
Names, Boundaries, and Arithmetic
The first query names the periods of 15 March 2026; the second finds its quarter's start and end, its day and week of the year, and the date two months later.
Example:
select calendar_year(d) as year, calendar_quarter(d) as quarter,
calendar_month(d) as month, calendar_week(d) as week, calendar_day(d) as day
from (select date '2026-03-15' as d);
select calendar_quarter_start_date(d) as q_start, calendar_quarter_end_date(d) as q_end,
calendar_day_of_year(d) as day_of_year, calendar_week_of_year(d) as week_of_year,
calendar_add_months(d, 2) as plus_2_months
from (select date '2026-03-15' as d);Output:
YEAR QUARTER MONTH WEEK DAY _______ __________ ___________ ___________ ______________ 2026 Q1-2026 MAR-2026 W11-2026 15-MAR-2026 Q_START Q_END DAY_OF_YEAR WEEK_OF_YEAR PLUS_2_MONTHS ______________ ______________ ______________ _______________ ________________ 01-JAN-2026 31-MAR-2026 74 11 15-MAY-2026
More Members
Example:
select calendar_month_start_date(d) as month_start,
calendar_month_end_date(d) as month_end,
calendar_week_start_date(d) as week_start,
calendar_add_weeks(d, 3) as plus_3_weeks,
calendar_quarter_of_year(d) as quarter_no
from (select date '2026-03-15' as d);
-- tickets per calendar quarter and month, named by the calendar functions
select calendar_quarter(b.booked_at) as quarter, calendar_month(b.booked_at) as month,
count(*) as bookings
from bookings b
group by all
order by min(b.booked_at);Output:
MONTH_START MONTH_END WEEK_START PLUS_3_WEEKS QUARTER_NO ______________ ______________ ______________ _______________ _____________ 01-MAR-2026 31-MAR-2026 12-MAR-2026 05-APR-2026 1 QUARTER MONTH BOOKINGS __________ ___________ ___________ Q4-2025 OCT-2025 7 Q4-2025 NOV-2025 81 Q4-2025 DEC-2025 164 Q1-2026 JAN-2026 222 Q1-2026 FEB-2026 154 Q1-2026 MAR-2026 72 6 rows selected.
Calendar weeks are counted from 1 January, so they start on that day's weekday: in 2026 a Thursday, which is why the week of 15 March starts on 12 March. The second query groups bookings by the quarter and month names, ready to print in a report, and orders the groups chronologically with min(booked_at).
Things to Know
- FISCAL_ functions have the same members for a year that starts on another day, and RETAIL_ functions for the 4-5-4 retail calendar.
- For ISO weeks that start on Monday, use the IW and IYYY format elements of TO_CHAR.
- Period names sort as text, not chronologically; order by a date or a number column instead.
Related Guides
Conclusion
The CALENDAR_ functions name the year, quarter, month, week, and day a date falls in, return the first and last day of each period, number days and periods within larger ones, and move dates by whole periods. They replace a lot of hand-built date logic in reports.
See my articles more often when you search Google.
