Introduction
Calendar functions, introduced in 23.26.1, provide us with the ability to format dates into standard year/quarter/month/week/day text for these calendars:
- Gregorian (Calendar): Standard Gregorian calendar.
- Fiscal: A fiscal calendar with a configurable fiscal-year start date.
- Retail: A retail date based on the National Retail Federation (NRF) 4-5-4 calendar.
Each formatted calendar function returns a VARCHAR2 value representing the input date at a particular level of the calendar hierarchy.
In this post, I will discuss the Calendar type functions and how to use them to create a date dimension table in a data warehouse star schema.
Calendar
These functions accept a date or any expression that can be implicitly converted to a date as input. They return a formatted string (VARCHAR2) representing the date, with default formats explicitly defined for each function. They include :
CALENDAR_YEARCALENDAR_QUARTERCALENDAR_MONTHCALENDAR_WEEKCALENDAR_DAY
See here for details on the default formats for each of these functions.
Creating a Date Dimension using Calendar functions
In many star schemas, a common dimension is a date dimension (dim_date). There are many different designs for these dimensions, but they generally contain calendar attributes for each date, including the year, quarter, month, week and day. These date dims help provide more detail to business dates when doing reporting and analysis. Additionally, before Oracle Database 23.26.1, developers relied on functions like TRUNC, ADD_MONTHS, TO_CHAR, or LAST_DAY and other date functions to derive these calendar attributes (an example is attached at the end of this post).
Calendar functions can greatly simplify this process by providing an easier way to derive calendar attributes when populating these dimensions.
Example
We will create a simple date dimension containing columns for the day, week, month, quarter and year using calendar functions.
Create the empty table
-- drop table if it exists
DROP TABLE IF EXISTS fact_sales ;
DROP TABLE IF EXISTS dim_date ;
DROP TABLE IF EXISTS dim_customer ;
DROP TABLE IF EXISTS dim_order ;
-- create empty table
CREATE TABLE DIM_DATE AS
select
TO_NUMBER(' ') as date_ID
,trunc(sysdate) as day
,calendar_week(sysdate) as week
,calendar_month(sysdate) as month
,calendar_quarter(sysdate) as quarter
,calendar_year(sysdate) as year
from dual
where 1=2;
ALTER TABLE dim_date MODIFY date_id PRIMARY KEY;
ALTER TABLE dim_date MODIFY day UNIQUE;SQL Code to generate the data and populate the table
The below code can be used to add data to the tables.
--generate for 2026-2027
with t_days(day) as (
VALUES(date'2025-12-31')
)
select to_number(to_char(day+level,'yyyymmdd')) as date_id
,day+level as day
,calendar_week(day+level) as week
,calendar_month(day+level) as month
,calendar_quarter(day+level) as quarter
,calendar_year(day+level) as year
from t_days
start with day = date'2025-12-31'
connect by level < (date'2027-01-01' - day) ;
DATE_ID DAY WEEK MONTH QUARTER YEAR
___________ ____________ ___________ ___________ __________ _______
20260101 01-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260102 02-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260103 03-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260104 04-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260105 05-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260106 06-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260107 07-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260108 08-JAN-26 W02-2026 JAN-2026 Q1-2026 2026
20260109 09-JAN-26 W02-2026 JAN-2026 Q1-2026 2026
20260110 10-JAN-26 W02-2026 JAN-2026 Q1-2026 2026
20260111 11-JAN-26 W02-2026 JAN-2026 Q1-2026 2026
...
-- insert into the dim_date
INSERT into dim_date
with t_days(day) as (
VALUES(date'2025-12-31')
)
select to_number(to_char(day+level,'yyyymmdd')) as date_id
,day+level as day
,calendar_week(day+level) as week
,calendar_month(day+level) as month
,calendar_quarter(day+level) as quarter
,calendar_year(day+level) as year
from t_days
start with day = date'2025-12-31'
connect by level < (date'2027-01-01' - day) ;
365 rows inserted.
COMMIT ;Examining the table generated
We can examine the data in our date dim using the following queries :
-- query the date dim
select * from dim_date ;
DATE_ID DAY WEEK MONTH QUARTER YEAR
___________ ____________ ___________ ___________ __________ _______
20260101 01-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260102 02-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260103 03-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
20260104 04-JAN-26 W01-2026 JAN-2026 Q1-2026 2026
...
-- filter for todays details
select * from dim_date
where day = trunc(sysdate) ;
DATE_ID DAY WEEK MONTH QUARTER YEAR
___________ ____________ ___________ ___________ __________ _______
20260814 14-AUG-26 W33-2026 AUG-2026 Q3-2026 2026
--filter for any day's details
select * from dim_date
where day = date'2026-04-24';
DATE_ID DAY WEEK MONTH QUARTER YEAR
___________ ____________ ___________ ___________ __________ _______
20260424 24-APR-26 W17-2026 APR-2026 Q2-2026 2026 How these date dimensions work in a Star Schema
To see how the date dimension is useful, we need a complete star schema.
Example
We run the below code to generate a sample star schema and insert data into the tables.
-- create other dimensions and fact
Create table dim_customer ( customer_id NUMBER PRIMARY KEY
,customer_no NUMBER UNIQUE
,customer_name VARCHAR2(32 CHAR)
,customer_city VARCHAR2(32 CHAR)
);
create table dim_order ( order_id NUMBER PRIMARY KEY
,order_no NUMBER
,customer_no NUMBER
,item_no NUMBER
,order_date DATE
,item_amt NUMBER
,UNIQUE (order_no, customer_no,item_no)
) ;
create table fact_sales (
sales_fact_id NUMBER GENERATED ALWAYS AS IDENTITY
,customer_id NUMBER REFERENCES dim_customer(customer_id)
,order_id NUMBER REFERENCES dim_order(order_id)
,date_id NUMBER REFERENCES dim_date(date_id)
,order_total NUMBER
);
--insert data
INSERT INTO dim_customer
VALUES (1,10,'John Doe','Atlantis')
,(2,20,'Sam Alo', 'Longstreet')
,(3,30,'Amin Alex','Sandpit')
,(4,40,'Kaey Al', 'New Town')
,(5,50,'Emma kong','Church Street') ;
INSERT INTO dim_order
VALUES (1,100,20,1,date'2026-01-20',20)
,(2,100,20,2,date'2026-01-20',10)
,(3,101,30,1,date'2026-03-11',05)
,(4,101,30,2,date'2026-03-11',30)
,(5,101,30,3,date'2026-03-11',40)
,(6,102,10,1,date'2026-04-02',55)
,(7,103,50,1,date'2026-06-18',15)
,(8,104,40,1,date'2026-07-05',12)
,(9,104,40,2,date'2026-07-05',16)
,(10,105,30,1,date'2026-07-01',80) ;
INSERT INTO fact_sales by name
SELECT
c.customer_id as customer_id
,o.order_id as order_id
,d.date_id as date_id
,sum(o.item_amt) as order_total
FROM
dim_customer c, dim_order o, dim_date d
WHERE
c.customer_no = o.customer_no
AND o.order_date = d.day
GROUP BY
c.customer_id
,o.order_id
,d.date_id
;
commit ;Notice the use of the BY NAME clause in the fact table insert statement.
Perform Star schema Data Analysis
We can use our star schema created to perform various data analyses as shown in the below examples.
Displaying all orders
We can display all orders recorded, the quarters and year in which they were recorded using the code below.
select
sf.order_id
,c.customer_name
,d.day
,d.quarter
,d.year
from
fact_sales sf
join to one (dim_customer c, dim_date d);
ORDER_ID CUSTOMER_NAME DAY QUARTER YEAR
___________ ________________ ____________ __________ _______
6 John Doe 02-APR-26 Q2-2026 2026
1 Sam Alo 20-JAN-26 Q1-2026 2026
2 Sam Alo 20-JAN-26 Q1-2026 2026
3 Amin Alex 11-MAR-26 Q1-2026 2026
4 Amin Alex 11-MAR-26 Q1-2026 2026
5 Amin Alex 11-MAR-26 Q1-2026 2026
10 Amin Alex 01-JUL-26 Q3-2026 2026
8 Kaey Al 05-JUL-26 Q3-2026 2026
9 Kaey Al 05-JUL-26 Q3-2026 2026
7 Emma kong 18-JUN-26 Q2-2026 2026
10 rows selected. Thanks to calendar functions, We can easily see the quarters and years of all orders.
Notice the use of JOIN TO ONE. The date dimension provides the calendar attributes, while JOIN TO ONE simplifies the joins between the fact table and its related dimensions. Because the fact table contains foreign keys referencing the primary keys of the dimensions, Oracle can infer the join relationships without the explicit ON clauses.
Display all orders from Q1-2026
We can filter for orders on a particular quarter, thanks to that data being populated in the dim_date table by the calendar function.
-- where quarter is q1-2026
select
sf.order_id
,c.customer_name
,d.day
,d.quarter
,sf.order_total
,d.year
from
fact_sales sf
join to one (dim_customer c, dim_date d)
WHERE d.quarter = 'Q1-2026';
ORDER_ID CUSTOMER_NAME DAY QUARTER ORDER_TOTAL YEAR
___________ ________________ ____________ __________ ______________ _______
1 Sam Alo 20-JAN-26 Q1-2026 20 2026
2 Sam Alo 20-JAN-26 Q1-2026 10 2026
3 Amin Alex 11-MAR-26 Q1-2026 5 2026
4 Amin Alex 11-MAR-26 Q1-2026 30 2026
5 Amin Alex 11-MAR-26 Q1-2026 40 2026 Total of all orders in Q1-2026
We can also compute the sum for all orders in a quarter
--total made in q1-2026
select
d.quarter
,sum(sf.order_total) quarter_total
from
fact_sales sf
join to one (dim_date d)
WHERE d.quarter = 'Q1-2026'
GROUP BY d.quarter ;
QUARTER QUARTER_TOTAL
__________ ________________
Q1-2026 105 Total in each quarter
We can also calculate the total sold in every quarter.
--total made in each quarter
select
d.quarter
,sum(sf.order_total) quarter_total
from
fact_sales sf
join to one (dim_date d)
GROUP BY d.quarter ;
QUARTER QUARTER_TOTAL
__________ ________________
Q1-2026 105
Q2-2026 70
Q3-2026 108 Calendar Add periods functions
There are also functions used to add or subtract time periods from dates. Previously, there was only the add_months function, used to add or subtract months to/from a given date. Starting from 23.6.1, We now have functions to do arithmetic on the year, quarter, month, week and day. They include:
- CALENDAR_ADD_YEARS
- CALENDAR_ADD_QUARTERS
- CALENDAR_ADD_MONTHS
- CALENDAR_ADD_WEEKS
- CALENDAR_ADD_DAYS
The first parameter is always a date, the second parameter the number of periods that should be added. Further, Negative values are also allowed for dates in the past. The return value is the calculated date. An example is shown below :
select
sysdate,
calendar_add_years(sysdate,1) next_year,
calendar_add_quarters(sysdate,1) next_quarter,
calendar_add_months(sysdate,1) next_month,
calendar_add_weeks(sysdate,1) next_week,
calendar_add_days(sysdate,1) next_day
/
SYSDATE NEXT_YEAR NEXT_QUAR NEXT_MONT NEXT_WEEK NEXT_DAY
--------- --------- --------- --------- --------- ---------
14-AUG-26 14-AUG-27 14-NOV-26 14-SEP-26 21-AUG-26 15-AUG-26
Other Calendar functions
For each calendar type, there are additional functions including :
FISCAL
RETAIL
Creating a Date Dimension prior to Oracle 26ai
The code below can be used to create a similar table prior to 23.26.1
-- Creating and populating a date dim prior to 23.26.1 and calendar functions
-- Date dimension for all dates in 2026
CREATE TABLE dim_date AS
SELECT
TO_NUMBER(to_char(day, 'yyyymmdd')) AS date_id,
qry_dates.day AS day,
to_char(qry_dates.day, '"W"IW-IYYY') AS week,
to_char(qry_dates.day, 'MON-YYYY') AS month,
to_char(qry_dates.day, '"Q"Q-YYYY') AS quarter,
to_char(qry_dates.day, 'YYYY') AS year
FROM
(
SELECT
DATE '2027-01-01' - rn AS day
FROM
(
SELECT
ROWNUM rn
FROM
dual
CONNECT BY
level <= DATE '2027-01-01' - DATE '2026-01-01'
)
) qry_dates;
-- WW can be used instead of IW for the week.
-- The IW follows the ISO standard, every Monday starts a new week.
-- The WW format mask follows a custom calendar based on the first day of the year.
-- Both could list up to 53 weeks.
-- Oracle docs Conclusion
As explored in this post, Calendar Functions provide an easier way to work with and perform calculations involving dates in Oracle Database. Additionally, we can employ these functions to help create date dimensions in Star Schemas. Furthermore, there exists the FISCAL and RETAIL type functions which were not explored in this post. Overall, a very neat suite of functions and much welcome addition to what is already and incredibly feature rich version of the database and one I’m sure will ease SQL development for many a developer.