First Impressions and Practical Observations
Oracle AI Database 26ai (23.26.1) introduces new CALENDAR SQL functions. Having written countless nested TRUNC/ADD_MONTHS/TO_CHAR/LAST_DAY expressions over the years, I expected to love them immediately. They do simplify many scenarios — yet they also introduce nuances that require careful understanding before using them everywhere.
This post focuses on syntax and behavior. In Part 2, I’ll examine performance and the broader modeling implications.
TL;DR
- CALENDAR/FISCAL/RETAIL functions can simplify many date calculations, especially for fiscal/retail calendars.
- The format masks are not the same as TO_CHAR/TO_DATE masks; some elements are required to keep results uniquely convertible.
- Some common needs (e.g. ISO week) don’t map cleanly to the new functions (at least in the examples shown).
- Performance and architectural implications—including the time dimension question—are covered in Part 2.
What are CALENDAR functions?
I never felt there is a lot you cannot get done about date calculations in Oracle SQL. Using some nested TO_CHAR, TO_DATE, EXTRACT, LAST_DAY, TRUNC, ADD_MONTHS, etc. – you name it – it was possible to do everything. Well, sometimes these expressions got really long and tricky. Things get even trickier if you have to deal with multiple date hierarchies like fiscal and/or retail calendars. CALENDAR functions are there to simplify and streamline those tasks. They can deal with the standard Gregorian calendar as well as with fiscal and retail calendars out of the box.
How to start? What does the term CALENDAR functions actually mean and how do you read the documentation? There is a good introduction chapter in the Database Development Book of the Oracle Documentation. You’ll find that there are three groups of functions for each of the supported calendar hierarchies, with their name starting with CALENDAR_…, FISCAL_…, and RETAIL_… respectively.
All groups contain a set of functions which can be further categorized as
- returning a string representing a period, like CALENDAR_WEEK
- returning dates, more precisely start and end dates of the periods like FISCAL_YEAR_END_DATE
- returning numeric values for the position of a period within its ancestor periods, like RETAIL_WEEK_OF_MONTH
- returning dates after performing period arithmetic, like CALENDAR_ADD_QUARTERS
- a SINCE-function returning human-readable text comparing a given date with SYSDATE and a utility function RETAIL_DAY_EXISTS
Yet another special aspect is that rather generic names are used for the functions even in the SQL reference, but in reality only specific forms of them exist. For example, you will see reference to CALENDAR_X_OF_Y, but there are nine specific functions like CALENDAR_DAY_OF_WEEK or CALENDAR_MONTH_OF_QUARTER, etc.
But let’s finally look at some examples. In Oracle’s sample schema SALES History (SH) we have the time dimension table SH.TIMES. We can try to see how to generate its column values with or without using the new CALENDAR functions. In Listing 1 you can see its content for one particular date January 1st, 2021. A SQL macro function PRINT_TABLE was used to display it.
SQL> with d as ( select * from sh.times
where time_id = date'2021-01-01')
select * from print_table(d)
COL_NAME COL_VALUE
----------------------- ------------------------
TIME_ID 2021-01-01 00:00:00
DAY_NAME Friday
DAY_NUMBER_IN_WEEK 5
DAY_NUMBER_IN_MONTH 1
CALENDAR_WEEK_NUMBER 1
FISCAL_WEEK_NUMBER 1
WEEK_ENDING_DAY 2021-01-03 00:00:00
WEEK_ENDING_DAY_ID 1472
CALENDAR_MONTH_NUMBER 1
FISCAL_MONTH_NUMBER 1
CALENDAR_MONTH_DESC 2021-01
CALENDAR_MONTH_ID 1696
FISCAL_MONTH_DESC 2021-01
FISCAL_MONTH_ID 1744
DAYS_IN_CAL_MONTH 31
DAYS_IN_FIS_MONTH 35
END_OF_CAL_MONTH 2021-01-31 00:00:00
END_OF_FIS_MONTH 2021-01-30 00:00:00
CALENDAR_MONTH_NAME January
FISCAL_MONTH_NAME January
CALENDAR_QUARTER_DESC 2021-01
CALENDAR_QUARTER_ID 1777
FISCAL_QUARTER_DESC 2021-01
FISCAL_QUARTER_ID 1793
DAYS_IN_CAL_QUARTER 91
DAYS_IN_FIS_QUARTER 91
END_OF_CAL_QUARTER 2021-03-31 00:00:00
END_OF_FIS_QUARTER 2021-03-26 00:00:00
CALENDAR_QUARTER_NUMBER 1
FISCAL_QUARTER_NUMBER 1
CALENDAR_YEAR 2021
CALENDAR_YEAR_ID 1804
FISCAL_YEAR 2021
FISCAL_YEAR_ID 1808
DAYS_IN_CAL_YEAR 365
DAYS_IN_FIS_YEAR 371
END_OF_CAL_YEAR 2021-12-31 00:00:00
END_OF_FIS_YEAR 2021-12-31 00:00:00
38 rows selected.
Listing 1: the row in the time dimension SH.TIMES
*_ID columns
Apart from the TIME_ID column, which is of type DATE and is the primary key of the table, all other *_ID numeric columns seem to be some surrogate keys, there is no description and I have no idea how they should be generated, so we will just ignore them.
*_NAME columns
Let’s first start with *_NAME columns: DAY_NAME, CALENDAR_MONTH_NAME, FISCAL_MONTH_NAME. Without new functions I would just use TO_CHAR(d, ‘Day’) to get the name of the weekday. Is there another way to do it? The first idea would be to use functions of the first category which are returning formatted text for calendar periods. For days, there is a CALENDAR_DAY function there.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_CHAR(d, 'Day') FROM dates
TO_CHAR(D,'DAY')
----------------
Friday
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_DAY(d) FROM dates
CALENDAR_DAY(D)
---------------
01-JAN-2021
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_DAY(d, 'Day') FROM dates
>> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_DAY(d, 'Day') FROM dates
*
Error at line 6
ORA-18620: date format missing one or more required elements
Help: https://docs.oracle.com/error-help/db/ora-18620/
Listing 2: the given format mask is not sufficient
The first try with the simplest form doesn’t give us the output we need. Okay, also with CALENDAR function we have a parameter to pass a format mask. But trying to pass the same format mask leads to an error ORA-18620. That’s the new one and it is associated specifically with CALENDAR functions. What does it mean, saying that something is missing or required? One important thing to understand is that the format mask to use with CALENDAR functions is not exactly the same as for TO_CHAR/TO_DATE functions, which we are familiar with. The reason for this is that it must be possible to use the returned formatted text representation of the period as a grouping criterion in analytic queries. That is, it should be unique at the supposed hierarchy level. That’s why depending on the level, some format mask parts are required. For example, the output of CALENDAR_MONTH cannot be just ‘January’ but must contain a year, as well as for CALENDAR_DAY it is not sufficient to have weekday name.
Another way to look at it is that it must be possible to convert the text back to a unique date within the hierarchy level using the same format mask. With TO_CHAR(d, ‘Day’) you can extract the weekday from every date value, but you cannot convert ‘Friday’ back to some unique date. Equally, using the format mask ‘Month’ you cannot convert ‘January’ back to a unique month. That makes sense.
For these three columns I would just stick with TO_CHAR implementation. FISCAL_MONTH_NAME will obviously be the same as CALENDAR_MONTH_NAME – ‘January’.
*_NUMBER columns
Now let’s go ahead with *_NUMBER columns. We could use both TO_CHAR as well as EXTRACT (sometimes) functions before. EXTRACT feels like the more modern one and – if we need a number – it already returns a number, no need for an additional TO_NUMBER call. For DAY_NUMBER_IN_WEEK the EXTRACT function will not work since it is giving us a day number in the month and not in the week.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_CHAR(d, 'D') AS DIY_DAY_NUMBER_IN_WEEK
, CALENDAR_DAY_OF_WEEK(d) as DAY_NUMBER_IN_WEEK
, (TRUNC(d) - TRUNC(d, 'IW') + 1) as ISO_DAY_NUMBER_IN_WEEK
FROM dates
DIY_DAY_NUMBER_IN_WEEK DAY_NUMBER_IN_WEEK ISO_DAY_NUMBER_IN_WEEK
---------------------- ------------------ ----------------------
6 6 5
1 row selected.
Listing 3: Calculation of the DAY_NUMBER_IN_WEEK
Both TO_CHAR and CALENDAR_DAY_OF_WEEK return 6 for Friday, but in SH.TIMES we can see 5. The output of both functions is dependent on NLS_TERRITORY setting. If set to ‘AMERICA’ they will return 6, if set to ‘GERMANY’ they’ll return 5. But I think often it is supposed to have an ISO weekday numbering in such time dimension tables. We can calculate it in a way that is independent from environment settings, also shown in Listing 3. But we cannot achieve this with CALENDAR function, as far as I can see.
Let’s go ahead and do it for DAY_NUMBER_IN_MONTH. That is the simple one. All three alternative calls return the same and for me it looks like a simplification, at least compared to the first call where one needs to memorize the format mask.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_NUMBER(TO_CHAR(d, 'DD')) AS DIY_DAY_NUMBER_IN_MONTH
, EXTRACT(DAY FROM d) AS DIY_DAY_NUMBER_IN_MONTH_2
, CALENDAR_DAY_OF_MONTH(d) AS DAY_NUMBER_IN_MONTH
FROM dates
DIY_DAY_NUMBER_IN_MONTH DIY_DAY_NUMBER_IN_MONTH_2 DAY_NUMBER_IN_MONTH
----------------------- ------------------------- -------------------
1 1 1
1 row selected.
Listing 4: Calculating DAY_NUMBER_IN_MONTH
Now coming to CALENDAR_MONTH_NUMBER and FISCAL_MONTH_NUMBER. For the first one it is as easy as for the day above, we have three simple alternatives. But we need special logic to take the fiscal year into account. Let’s say the fiscal year starts on 1st of October. Then calendar month 10 should be the first in the fiscal year, so that it will be fine to first subtract nine months from the given date.
With new functions in 26ai we have FISCAL_MONTH_OF_YEAR. As its second parameter it is accepting the date for the start of the fiscal year. But as you can see in Listing 5, just passing this starting date, we will get 1 for January. The FISCAL functions accept the third parameter INDEX_BY to distinguish the numbering between absolute (value ‘DATE’, default) and position based within the fiscal year (value ‘POSITION’). Hence, we need to pass ‘POSITION’ to get 4 as an answer for January.
If you don’t want to pass the starting date for the fiscal year to each and every FISCAL_… function, you can set a session or system parameter CALENDAR_FISCAL_YEAR_START as shown in line 23 (only month and day are relevant). But then there is one subtle problem with it: you can’t skip the parameter, if you want to use ‘POSITION’ as INDEX_BY parameter, you have to pass NULL instead (see line 28)
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_NUMBER(TO_CHAR(d, 'MM')) AS DIY_CALENDAR_MONTH_NUMBER
, EXTRACT(MONTH FROM d) AS DIY_CALENDAR_MONTH_NUMBER_2
, CALENDAR_MONTH_OF_YEAR(d) AS DAY_CALENDAR_MONTH_NUMBER
FROM dates
DIY_CALENDAR_MONTH_NUMBER DIY_CALENDAR_MONTH_NUMBER_2 DAY_CALENDAR_MONTH_NUMBER
------------------------- --------------------------- -------------------------
1 1 1
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT EXTRACT(MONTH FROM ADD_MONTHS(d, -9)) AS DIY_FISCAL_MONTH_NUMBER
, FISCAL_MONTH_OF_YEAR(d, DATE '2020-10-01') AS FISCAL_MONTH_NUMBER
, FISCAL_MONTH_OF_YEAR(d, DATE '2020-10-01','POSITION') as FISCAL_MONTH_NUMBER_POS
FROM dates
DIY_FISCAL_MONTH_NUMBER FISCAL_MONTH_NUMBER FISCAL_MONTH_NUMBER_POS
----------------------- ------------------- -----------------------
4 1 4
1 row selected.
SQL> ALTER SESSION SET CALENDAR_FISCAL_YEAR_START = '01-OCT-2020', 'DD-MON-YYYY'
Session altered.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT FISCAL_MONTH_OF_YEAR(d) as FISCAL_MONTH_NUMBER
, FISCAL_MONTH_OF_YEAR(d, null, 'POSITION') AS FISCAL_MONTH_NUMBER_POS
FROM dates
FISCAL_MONTH_NUMBER FISCAL_MONTH_NUMBER_POS
------------------- -----------------------
1 4
1 row selected.
Listing 5: Calendar and fiscal month numbers
Now to the interesting part: the week numbers! Choosing between TO_NUMBER(TO_CHAR(d, ‘W’)) and CALENDAR_WEEK_OF_YEAR(d), they both return the same result, I would say the latter is a simpler way. At least if you are in Europe, you will typically need an ISO-week. According to ISO-8601, you can count a week as a whole if it contains a Thursday. The 1st of January 2021 was a Friday, that’s why it still belongs to calendar week 53 of the year 2020! TO_NUMBER(TO_CHAR(d, ‘IW’)) clearly shows that. The “I” in the format mask “IW” stands for ISO weeks. But, if I haven’t overlooked something, I’ve found no way to do it with the new CALENDAR_WEEK_OF_YEAR function so far.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_NUMBER(TO_CHAR(d, 'W')) AS diy_calendar_weekr
, TO_NUMBER(TO_CHAR(d, 'IW')) AS diy_iso_calendar_week
, CALENDAR_WEEK_OF_YEAR(d) AS calendar_week
FROM dates
DIY_CALENDAR_WEEKR DIY_ISO_CALENDAR_WEEK CALENDAR_WEEK
------------------ --------------------- -------------
1 53 1
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TRUNC((d - ADD_MONTHS(TRUNC(ADD_MONTHS(d, -9), 'YYYY'), 9)) / 7) + 1 diy_fiscal_week
, FISCAL_WEEK_OF_YEAR(d) AS fiscal_week
FROM dates
DIY_FISCAL_WEEK FISCAL_WEEK
--------------- -----------
14 14
1 row selected.
Listing 6: Calendar and fiscal week numbers
Also note that the Do-It-Yourself calculation of the fiscal week number got quite complex. I am showing just one possible solution, but handling the day and month of the fiscal year start as well as dates in both the same and the next calendar year makes this expression challenging. The new function FISCAL_WEEK_OF_YEAR is indeed a great simplification here.
*_DESC columns
For description columns we will again use the first category of CALENDAR functions providing formatted text and let’s take a closer look at format mask usage. For the month description we need a string in YYYY-MM format. No problem to do it with TO_CHAR, but as you see in Listing 7, the default format when using CALENDAR_MONTH will be MON-YYYY. Therefore we need to explicitly provide the format mask needed. And the same problem with quarters as well. So, just in terms of a call complexity we have no change using new functions.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_CHAR(d, 'YYYY-MM') AS DIY_CALENDAR_MONTH_DESC
, CALENDAR_MONTH(d) AS calendar_month_desc_wrong
, CALENDAR_MONTH(d, 'YYYY-MM') AS calendar_month_desc
from dates
DIY_CALENDAR_MONTH_DESC CALENDAR_MONTH_DESC CALENDAR_MONTH_DESC_1
----------------------- ------------------- ---------------------
2021-01 JAN-2021 2021-01
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_CHAR(d, 'YYYY-"0"Q') AS diy_calendar_quarter_desc
, CALENDAR_QUARTER(d) AS calendar_quarter_desc_wrong
, CALENDAR_QUARTER(d,'YYYY-"0"Q') AS calendar_quarter_desc
FROM dates
DIY_CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC_1
------------------------- --------------------- -----------------------
2021-01 Q1-2021 2021-01
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TO_CHAR(d, 'Month-YYYY') AS diy_calendar_month_desc
, TO_CHAR(d, 'fmMonth-YYYY') AS diy_calendar_month_desc_wo_blanks
, CALENDAR_MONTH(d, 'Month-YYYY') AS calendar_month_desc
FROM dates
DIY_CALENDAR_MONTH_DESC DIY_CALENDAR_MONTH_DESC_WO_BLANKS CALENDAR_MONTH_DESC
----------------------- --------------------------------- -------------------
January -2021 January-2021 January -2021
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_MONTH(d, 'fmMonth-YYYY') AS calendar_month_desc
FROM dates
>> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_MONTH(d, 'fmMonth-YYYY') AS calendar_month_desc
FROM dates
*
Error at line 23
ORA-01821: date format not recognized
Help: https://docs.oracle.com/error-help/db/ora-01821/
Listing 7: Generating period descriptions
But let’s show another “good-to-know” point in Listing 7. If we want the name of the month like ‘January’, we’ll use ‘Month-YYYY’. But as you may have noticed the names will be right padded with blanks to the longest month name in your NLS_DATE_LANGUAGE, i.e., September or 9 characters. We can get rid of this padding by adding the format modifier “fm” to our format mask. But unfortunately it is not possible to do the same with the CALENDAR_MONTH function.
END_OF_* columns
This is again going to be interesting. Let’s start with weeks. For DIY approach the logic is to truncate the given date to the starting day of the week and then add six days. But what is the starting day of the week? It really depends on how you define a “week”. You can say, the first week of the year starts on whatever weekday the 1st of January is and then add seven days, the 2nd week starts on the same weekday and so on. That is what Oracle is doing if you truncate a date with format mask ‘WW’. Or you can do TRUNC(d, ‘D’) – this would be territory dependent, for US the week starts on Sunday but for Germany on Monday. With TRUNC(d, ‘W’) it will be as the first day of the month. And what we see in SH.TIMES looks like ISO weeks for me. That is we need to do TRUNC(d, ‘IW’). Okay, with Do-It-Yourself approach we can choose whatever we need, but what about CALENDAR_WEEK_END_DATE? It seems to work as ‘WW’ and there is no parameter to change this behavior.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT TRUNC(d, 'IW') + 6 week_ending_day_IW
, TRUNC(d, 'WW') + 6 week_ending_day_WW
, TRUNC(d, 'W') + 6 week_ending_day_W
, TRUNC(d, 'D')+6 week_ending_day_D
FROM dates
WEEK_ENDING_DAY_IW WEEK_ENDING_DAY_WW WEEK_ENDING_DAY_W WEEK_ENDING_DAY_D
------------------ ------------------ ----------------- -----------------
03.01.21 07.01.21 07.01.21 03.01.21
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_WEEK_END_DATE(d) AS week_ending_day
FROM dates
WEEK_ENDING_DAY
---------------
07.01.21
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT LAST_DAY(d) As diy_end_of_cal_month
, CALENDAR_MONTH_END_DATE(d) AS end_of_cal_month
, ADD_MONTHS(TRUNC(d, 'Q'), 3) - 1 AS diy_end_of_cal_quarter
, CALENDAR_QUARTER_END_DATE(d) AS end_of_cal_quarter
, ADD_MONTHS(TRUNC(d, 'YYYY'), 12) - 1 AS diy_end_of_cal_year
, CALENDAR_YEAR_END_DATE(d) AS end_of_cal_year
FROM dates
DIY_END_OF_CAL_MONTH END_OF_CAL_MONTH DIY_END_OF_CAL_QUARTER END_OF_CAL_QUARTER DIY_END_OF_CAL_YEAR END_OF_CAL_YEAR
-------------------- ---------------- ---------------------- ------------------ ------------------- ---------------
31.01.21 31.01.21 31.03.21 31.03.21 31.12.21 31.12.21
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT FISCAL_MONTH_END_DATE(d) AS end_of_fiscal_month
, FISCAL_QUARTER_END_DATE(d) AS end_of_cal_quarter
, FISCAL_YEAR_END_DATE(d) AS end_of_cal_year
FROM dates
END_OF_FISCAL_MONTH END_OF_CAL_QUARTER END_OF_CAL_YEAR
------------------- ------------------ ---------------
31.01.21 31.03.21 30.09.21
1 row selected.
Listing 8: End of the week, quarter and month
With calendar months, quarters and years there seem to be no surprises. We can use LAST_DAY() for the month and need to do some date arithmetic for quarter and year, so that using the new CALENDAR functions looks simpler.
But speaking about fiscal periods… Well, if the fiscal year starts at calendar quarter boundaries like on October 1st, then the end of the fiscal periods will be the same as of calendar periods. But if your fiscal year starts at September 1st? The Do-It-Yourself expressions become quite complex and I’m just happy we have these FISCAL_… functions now.
DAYS_IN_* columns
For the last block of columns we need to calculate the number of days in respective period and the expressions are again looking complex (Listing 9). Switching to CALENDAR-functions: even though we need to use two functions for each calculation, it looks more straightforward.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT LAST_DAY(d) - TRUNC(d,'MM') + 1 AS diy_days_in_cal_month
, (ADD_MONTHS(TRUNC(d,'Q'), 3) - 1) - TRUNC(d,'Q') + 1 AS diy_days_in_cal_quarter
, (ADD_MONTHS(TRUNC(d,'YYYY'), 12) - 1) - TRUNC(d,'YYYY') + 1 AS diy_days_in_cal_year
FROM dates
DIY_DAYS_IN_CAL_MONTH DIY_DAYS_IN_CAL_QUARTER DIY_DAYS_IN_CAL_YEAR
--------------------- ----------------------- --------------------
31 90 365
1 row selected.
SQL> WITH dates (d) as (VALUES (date '2021-01-01' ))
SELECT CALENDAR_MONTH_END_DATE(d) - CALENDAR_MONTH_START_DATE(d) + 1 AS days_in_cal_month
, CALENDAR_QUARTER_END_DATE(d) - CALENDAR_QUARTER_START_DATE(d) + 1 AS days_in_cal_quarter
, CALENDAR_YEAR_END_DATE(d) - CALENDAR_YEAR_START_DATE(d) + 1 AS days_in_cal_year
FROM dates
DAYS_IN_CAL_MONTH DAYS_IN_CAL_QUARTER DAYS_IN_CAL_YEAR
----------------- ------------------- ----------------
31 90 365
1 row selected.
Listing 9: Calculating Days in …
It is impossible to cover all use cases and complete syntax and actually it wasn’t my intention in this blog post. For this you can still read the Oracle documentation.
The new CALENDAR functions are not just syntactic sugar, they clearly reduce verbosity and encapsulate complex fiscal and retail logic that previously required non-trivial expressions. However, they also introduce behavioral nuances — especially around formatting, ISO conventions, and NLS dependencies — that require careful attention.
In the next post, I’ll step back from syntax and explore execution context and architecture. If date calculations have become this simple, do we still need a dedicated time dimension?