14 Advanced SQL Extensions: Calendar Functions and Aggregation Filters

The Advanced SQL Extensions: Calendar Functions and Aggregation Filters chapter introduces powerful enhancements to Oracle SQL that simplify the way developers write and manage analytic queries. These extensions are part of the broader Select for Analysis (SfA) initiative, designed to reduce verbosity, improve readability, and align Oracle SQL more closely with industry standards.

Traditional analytic queries often require complex combinations of CASE expressions, nested subqueries, or custom logic to handle time hierarchies and conditional aggregations. The new extensions address these challenges directly:

  • Calendar Functions provide built-in support for extracting and formatting time-based information across multiple hierarchies, which include Gregorian calendar, fiscal calendar, and retail (NRF 4-5-4) calendar. They allow developers to group, filter, and navigate dates with concise syntax while ensuring outputs remain uniquely convertible back to valid dates.

  • Aggregation Filters introduce the ANSI SQL-standard FILTER WHERE clause into Oracle SQL. This enables developers to apply per-aggregation conditions directly within queries, eliminating the need for verbose subqueries or error-prone CASE expansions. The result is cleaner, more intuitive code that still produces correct results under complex scenarios, including data redaction.

Together, these extensions empower developers to:

  • Write shorter, more expressive queries for time-based analysis.

  • Manage fiscal and retail calendars as integral components of SQL.

  • Apply conditional filters to aggregations without sacrificing clarity or correctness.

  • Maintain compatibility with SQL standards, ensuring portability and easier adoption.

By mastering these features, developers can streamline analytic reporting, reduce query complexity, and unlock new levels of flexibility in Oracle AI Database 26ai.

14.1 Calendar Functions

Calendar functions are designed to simplify analytic queries by allowing developers to extract and manipulate time-based information across different calendar hierarchies directly from date columns. These functions support standard calendar (Gregorian), fiscal, and retail hierarchies, enabling flexible reporting across different business contexts. These functions are critical for analytic queries, enabling grouping and navigation across different time dimensions while ensuring consistency and correctness.

  • Analytic Queries: Developers often need to group or filter data by calendar periods. These functions provide concise syntax for such operations.

  • Multiple Hierarchies: A single date column can represent different hierarchies (calendar, fiscal, retail). Functions allow switching between them seamlessly.

  • Consistency: Outputs are guaranteed to be convertible back to a DATE using the same format string, ensuring reliable densification and analysis.

The functions return either:

  • Formatted text values (VARCHAR2) uniquely identifying a time period, or

  • Date values (DATE) representing the start or end of a period.

These functions are modeled to behave similarly to existing SQL row functions like EXTRACT, but with enhanced semantics for calendar hierarchies. They are accessible anywhere SQL row functions are allowed, including within PL/SQL queries.

14.1.1 Using Calendar Functions

In analytic queries, a single date column in a fact table often serves as the time dimension.

This time dimension may represent:

  • A standard calendar hierarchy (Gregorian calendar).

  • A fiscal hierarchy, where the year starts on a custom date.

  • A retail hierarchy, such as the National Retail Federation (NRF) 4-5-4 calendar.

To support these use cases, Oracle introduces a suite of calendar functions. These functions extend the capabilities of existing EXTRACT row functions, allowing developers to retrieve level-specific information (year, quarter, month, week, day) across multiple calendar types.

Functions are introduced for all combinations of calendar, fiscal, and retail hierarchies with day, week, month, quarter, year levels. These functions accept a DATE expression, or any expression that may be implicitly converted to DATE. The return value of each function is a formatted string representing the date, with default formats explicitly defined for each function.

General Syntax

The following is a generalized version of the syntax used in calendar functions:

CALENDAR (
    <date_expression>
    [ INDEX_BY <column_or_expression> ]
    [ IS_RESTATED <boolean_flag> ]
    [ START_DATE <date> ]
    [ END_DATE <date> ]
    [ INTERVAL <time_unit> ]
)
Parameter details

  • INDEX_BY defines the column or expression used to group or index the calendar output. Example: INDEX_BY month organizes results by month.

  • IS_RESTATED is a Boolean flag (TRUE / FALSE) indicating whether the values are restated (adjusted) compared to original reporting. This is useful in financial analysis to distinguish between raw and adjusted data.

  • START_DATE / END_DATE specify the calendar range boundaries.

  • INTERVAL sets the granularity of the calendar (DAY, MONTH, QUARTER, YEAR).

Calendar Function Categories

The calendar functions are grouped into the following categories:

  • Formatted Text Functions return a VARCHAR2 string representing the period (calendar, fiscal, retail) for year, quarter, month, week, or day. Examples: CALENDAR_YEAR, FISCAL_MONTH, RETAIL_QUARTER.

  • Start End Date Functions return DATE values (time set to midnight) for period boundaries; the start or end date of a given period. Examples: CALENDAR_QUARTER_START_DATE, FISCAL_YEAR_END_DATE, RETAIL_MONTH_START_DATE.

  • X of Y Functions return numeric values representing the position of a period within its ancestor periods. Examples: CALENDAR_DAY_OF_WEEK, FISCAL_MONTH_OF_YEAR, RETAIL_WEEK_OF_MONTH

  • Add X Periods Functions perform period arithmetic and return DATE values while preserving time components. Examples: CALENDAR_ADD_MONTHS, FISCAL_ADD_MONTHS, RETAIL_ADD_DAYS

  • Since and Utility Functions compute human‑readable intervals between a given date and SYSDATE and use utility function such as RETAIL_DAY_EXISTS

When to Use these Functions

  • Grouping or aggregating by calendar, fiscal, or retail periods in analytic queries.

  • Densification and time series operations where a one‑to‑one mapping between a period label and a DATE is required.

  • Converting a single date column into multiple calendar hierarchies (calendar, fiscal, retail) without maintaining separate time dimension tables.

  • Performing period navigation (For example, add months, add weeks) while preserving time-of-day components.

The subsequent sections provide a detailed description of each calendar function category, supplemented with verbatim examples.

See Also:

Calendar Functions in Oracle AI Database SQL Language Reference for syntactic and semantic information about calendar functions

14.1.2 Formatted Text Functions

Formatted text functions allow developers to obtain groupings for various grains of detail (year, quarter, month, week, day) within calendar, fiscal, and retail hierarchies. The output is formatted text similar to TO_CHAR, but with stricter rules to ensure uniqueness and reversibility back to a DATE. The format string must include required specifiers so the output can be converted back to a DATE at the function’s grain using the same format string.

These functions allow grouping by calendar hierarchies, for example: computing sales by calendar year, fiscal year, or retail year. The following SQL queries illustrate how calendar functions can be used to aggregate sales data across different time hierarchies (calendar year, fiscal year, and retail year) from the same date column in a fact table.

SELECT calendar_year(f.day), region, SUM(f.sales)
FROM sales_fact f
JOIN TO ONE (customer_tbl)
GROUP BY ALL;

SELECT fiscal_year(f.day), region, SUM(f.sales)
FROM sales_fact f
JOIN TO ONE (customer_tbl)
GROUP BY ALL;

SELECT retail_year(f.day), region, SUM(f.sales)
FROM sales_fact f
JOIN TO ONE (customer_tbl)
GROUP BY ALL;

These examples demonstrate that the same date column (f.day) can be flexibly interpreted according to different calendar hierarchies. By using calendar_year, fiscal_year, or retail_year, analysts can generate consistent aggregations tailored to business needs without restructuring the underlying data.

The formatted text functions are grouped into three categories:

  • Calendar Functions (Gregorian calendar)

  • Fiscal Functions (Gregorian calendar offset by fiscal year start)

  • Retail Functions (NRF 4‑5‑4 retail calendar)

14.1.2.1 Calendar Functions

Calendar functions operate on the standard Gregorian calendar. Format specifiers behave exactly as they do in TO_CHAR.

Table 14-1 Calendar Functions Summary

Function Description Required Formats Default

CALENDAR_YEAR

Returns a formatted year label for the Gregorian calendar (VARCHAR2)

Requires a 4‑digit year format (YYYY/SYYYY)

Default format is SYYYY; Example: 2025.

CALENDAR_QUARTER

Returns a quarter label for the Gregorian calendar (VARCHAR2)

Requires quarter (Q) and 4‑digit year (Q and YYYY or SYYYY)

"Q"Q-SYYYY

Example: Q1-2024

CALENDAR_MONTH

Returns a month label for the Gregorian calendar (VARCHAR2)

Requires a 4‑digit year (YYYY or SYYYY) and one of MM/MON/MONTH

MON-SYYYY. Example: JAN-2024

CALENDAR_WEEK

Returns a week label for the Gregorian calendar (VARCHAR2)

Requires a 4‑digit year (YYYY or SYYYY) and WW

"W"WW-SYYYY. Example W01-2024

CALENDAR_DAY

Returns a day label for the Gregorian calendar (VARCHAR2)

Requires a 4‑digit year, month (MM/MON/MONTH, and an appropriate day specifier (DD/DDD/DY/DAY)

DD-MON-SYYYY. Example: 01-JAN-2024.

Example 14-1 Calendar Year

This example shows how to extract the calendar year from a date:

SELECT CALENDAR_YEAR(DATE '2025-03-15', 'YYYY') YEAR
FROM DUAL;

The following output is generated:

YEAR
----
2025

Example 14-2 Two-digit Year (using End Date + TO_CHAR) 

SELECT TO_CHAR(CALENDAR_YEAR_END_DATE(DATE '2025-03-15'), 'YY') YEAR
FROM DUAL;

The following output is generated:

YEAR
----
25
14.1.2.2 Fiscal Functions

Fiscal hierarchies offset the Gregorian calendar by a fiscal year start date.

  • The start date can be specified as a function argument (fis_year_start) or using the CALENDAR_FISCAL_YEAR_START parameter.

  • A fiscal year is determined by the calendar year in which its final day falls.

Table 14-2 Fiscal Functions Summary

Function Description Required Formats Default

FISCAL_YEAR

Returns a fiscal year label (VARCHAR2); fiscal year is determined by the last day of the fiscal year

Requires a 4‑digit year (YYYY or SYYYY)/ Accepts optional fis_year_start to override session/system parameter

"FY"SYYYY. Example: FY2024

FISCAL_QUARTER

Returns a fiscal quarter label (VARCHAR2); quarter numbering is relative to fiscal year start

Requires Q and 4‑digit year (YYYY or SYYYY)

"Q"Q-"FY"SYYYY. Example: Q1-FY2024

FISCAL_MONTH

Returns a fiscal month label (VARCHAR2); requires 4‑digit year and one of MM/MON/MONTH; month and year are determined by the last day of the fiscal month

Requires a 4‑digit year (YYYY or SYYYY)/Accepts optional fis_year_start

MON-"FY"SYYYY. Example: JAN-FY2024

FISCAL_WEEK

Returns a fiscal week label (VARCHAR2); week numbering is relative to fiscal year start

Requires WW and 4‑digit year (YYYY or SYYYY); accepts optional fis_year_start

"W"WW-"FY"SYYYY. Example: W01-FY2024

FISCAL_DAY

Returns a fiscal day label (VARCHAR2); requires a 4‑digit year and appropriate day specifier

Requires a 4‑digit year, month (MM/MON/MONTH, WW, and an appropriate day specifier (DD/DDD/DY/DAY); accepts optional fis_year_start

DD-MON-"FY"SYYYY. Example: 01-JAN-FY2024

Example 14-3 Fiscal Year Start = June 1

The following example shows the fiscal year for dates with the fiscal year start of June 1:

SELECT FISCAL_YEAR(DATE '2025-05-15', DATE '2025-06-01') MAY_FY,
       FISCAL_YEAR(DATE '2025-06-15', DATE '2025-06-01') JUN_FY
FROM DUAL;

The following output is generated:

MAY_FY   JUN_FY
------   ------
FY2025   FY2026

The last day of the fiscal year that contains May 15, 2025 is May 31, 2025, therefore it’s in fiscal year 2025. The last day of the fiscal year that contains June 15, 2025 is May 31, 2026, therefore it’s in fiscal year 2026.

Example 14-4 Fiscal Month Start = June 5

When the fiscal year starts on a day other than the 1st of the month, the aforesaid logic extends into months too. The following example shows months when the fiscal year starts on June 5:

SELECT FISCAL_MONTH(DATE '2025-05-15', DATE '2025-06-05') MON1,
       FISCAL_MONTH(DATE '2025-06-01', DATE '2025-06-05') MON2,
       FISCAL_MONTH(DATE '2025-06-15', DATE '2025-06-05') MON3
FROM DUAL;

The following output is generated:

MON1        MON2        MON3
----------  ----------  ----------
JUN-FY2025  JUN-FY2025  JUL-FY2026
14.1.2.3 Retail Functions

Retail functions implement the NRF 4‑5‑4 calendar.

  • Retail year begins on a Sunday (Jan 29 – Feb 4).

  • Leap weeks (53rd week) are added every 5–6 years.

  • Optional is_restated parameter handles restated years (52 weeks).

Table 14-3 Retail Functions Summary

Function Description Required Formats Default

RETAIL_YEAR

Returns a retail year label using the NRF 4‑5‑4 rules (VARCHAR2)

Requires a 4‑digit year (YYYY or SYYYY)/Accepts optional is_restated

"RY"SYYYY. Example: RY2024

RETAIL_QUARTER

Returns a retail quarter label (VARCHAR2); quarter defined by retail months (weeks) and retail year rules

Requires Q and 4‑digit year (YYYY or SYYYY)/Accepts optional is_restated

"Q"Q-"RY"SYYYY. Example: Q1-RY2024.

RETAIL_MONTH

Returns a retail month label (VARCHAR2); months are defined in whole weeks per NRF 4‑5‑4

Requires a 4‑digit year (YYYY or SYYYY) and one of MM/MON/MONTH/ Accepts optional is_restated

MON-"RY"SYYYY. Example: FEB-RY2024

RETAIL_WEEK

Returns a retail week label (VARCHAR2); weeks start on Sunday and are numbered within the retail year

Requires WW and 4‑digit year (YYYY or SYYYY); Accepts optional is_restated

 "W"WW-"RY"SYYYY. Example: W01-RY2024
RETAIL_DAY

Returns a retail day label (VARCHAR2); default uses day-of-week and week number

Requires a 4‑digit year, month (MM/MON/MONTH, WW, and an appropriate day specifier (DD/DDD/DY/DAY)

DY-"W"WW-"RY"SYYYY. Example: SUN-W01-RY2024

Example 14-5 Leap Week in Retail Year 2023

This example shows how a 53rd week is added when necessary:

-- Retail year 2022 (normal 52 weeks)
Last day of 52nd week: January 28, 2023 → 3 days remain → no leap week

-- Retail year 2023 (53 weeks)
Last day of 52nd week: January 27, 2024 → 4 days remain → leap week added (Jan 28 – Feb 3, 2024)

Example 14-6 Restated Retail Year Error

When using a restated retail year, invalid dates raise errors:

SELECT   RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month , 
         SUM ( sales ) sales 
FROM fact 
GROUP BY ALL 
ORDER BY 1 ;

The following output is generated:

-- ORA - xxx: The day does not exist in the restated retail calendar

Example 14-7 Protecting Calls with RETAIL_DAY_EXISTS

This example shows how to filter invalid rows using RETAIL_DAY_EXISTS: 

SELECT   RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month , 
         SUM ( sales ) sales 
FROM fact 
WHERE RETAIL_DAY_EXISTS ( order_date , 'RESTATED' ) = TRUE 
GROUP BY ALL 
ORDER BY 1 ;

The following output is generated:


MONTH      SALES
---------- -----
FEB - RY2023 123

Example 14-8 Protecting Calls with CASE Expression for Invalid Days

Alternatively, developers can use a CASE expression: 

SELECT   CASE 
           WHEN RETAIL_DAY_EXISTS ( order_date , 'RESTATED' ) = TRUE 
           THEN RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) 
           ELSE ' #INVALID_DAY#' 
         END month , 
         SUM ( sales ) sales 
FROM fact 
GROUP BY ALL 
ORDER BY 1 ;

The following output is generated:

MONTH          SALES
-------------  -----
FEB - RY2023   123
#INVALID_DAY#  456

Example 14-9 Retail Month Aggregation

This example shows the retail month aggregation with restated calendar and error handling:

SELECT   RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month ,
         SUM ( sales ) sales
FROM fact
GROUP BY ALL
ORDER BY 1 ;

14.1.3 Start End Date Functions

The start end date functions return the start or end DATE (midnight) for the specified period (year, quarter, month, week) and calendar variant (calendar, fiscal, retail). The returned value is a DATE with time components set to midnight. These functions are useful for range calculations, densification, and aligning time windows.

  • Fiscal variants accept function argument fis_year_start or use the session/system parameter CALENDAR_FISCAL_YEAR_START.

  • Retail variants accept is_restated.

See Also:

Calendar Functions in Oracle AI Database SQL Language Reference for syntactic and semantic information about start end date functions

Table 14-4 Start End Date Functions Summary

Function Description

CALENDAR_YEAR_START_DATE/ CALENDAR_YEAR_END_DATE

Returns the Gregorian start/end DATE for the calendar year containing the input date.

CALENDAR_QUARTER_START_DATE / CALENDAR_QUARTER_END_DATE

Returns the Gregorian start/end DATE for the calendar quarter containing the input date.

CALENDAR_MONTH_START_DATE / CALENDAR_MONTH_END_DATE

Returns the Gregorian start/end DATE for the calendar month containing the input date.

CALENDAR_WEEK_START_DATE / CALENDAR_WEEK_END_DATE

Returns the Gregorian start/end DATE for the calendar week containing the input date.

FISCAL_YEAR_START_DATE/ FISCAL_YEAR_END_DATE

Returns the fiscal start/end DATE for the calendar year containing the input date; accepts optional fis_year_start or uses CALENDAR_FISCAL_YEAR_START session/system parameter.

FISCAL_QUARTER_START_DATE / FISCAL_QUARTER_END_DATE

Returns fiscal quarter boundaries; accepts optional fis_year_start.

FISCAL_MONTH_START_DATE / FISCAL_MONTH_END_DATE

Returns fiscal month boundaries; accepts optional fis_year_start.

FISCAL_WEEK_START_DATE / FISCAL_WEEK_END_DATE

Returns fiscal week boundaries; accepts optional fis_year_start.

RETAIL_YEAR_START_DATE / RETAIL_YEAR_END_DATE

Returns retail year boundaries per NRF 4‑5‑4 rules; accepts optional is_restated.

RETAIL_QUARTER_START_DATE / RETAIL_QUARTER_END_DATE

Returns retail quarter boundaries; accepts optional is_restated.

RETAIL_MONTH_START_DATE / RETAIL_MONTH_END_DATE

Returns retail month boundaries (weeks); accepts optional is_restated.

RETAIL_WEEK_START_DATE / RETAIL_WEEK_END_DATE

Returns retail week boundaries; accepts optional is_restated.

Example 14-10 Start and End Date Functions

SELECT   CALENDAR_QUARTER_START_DATE ( DATE ' 2025 - 06 - 15 ' ) cqs ,
         FISCAL_YEAR_END_DATE ( DATE ' 2025 - 06 - 15 ' , DATE ' 2025 - 06 - 01 ' ) fye ,
         RETAIL_MONTH_START_DATE ( DATE ' 2025 - 06 - 15 ' ) rms
FROM DUAL ;

The following output is generated:


CQS              FYE              RMS
-----------      -----------      -----------
01 - APR - 2025  31 - MAY - 2026  01 - JUN - 2025

14.1.4 X of Y Functions

X of Y functions return numeric positions (NUMBER) representing a particular component of a date within an ancestor period. CALENDAR_MONTH_OF_YEAR, FISCAL_DAY_OF_MONTH, and RETAIL_WEEK_OF_MONTH are a few examples representing calendar month of year, fiscal of month, and retail week of month respectively. Because the year component has no ancestor, those functions are simply CALENDAR_YEAR_NUMBER, FISCAL_YEAR_NUMBER, and RETAIL_YEAR_NUMBER.

Some functions accept an index_by parameter ('DATE' or 'POSITION') to choose between positional numbering and date‑based numbering:

  • index_by may be 'DATE' (follows NLS territory) or 'POSITION' (positional within the week/month).

  • Fiscal and retail variants accept their respective optional parameters.

See Also:

Calendar Functions in Oracle AI Database SQL Language Reference for syntactic and semantic information about X of Y functions

Table 14-5 X of Y Functions Summary

Function Description

CALENDAR_YEAR_NUMBER

Numeric calendar year number for the input date

CALENDAR_QUARTER_OF_YEAR

Quarter number (1–4) within the calendar year

CALENDAR_MONTH_OF_YEAR

Month number (1–12) within the calendar year

CALENDAR_MONTH_OF_QUARTER

Month number within the quarter

CALENDAR_WEEK_OF_YEAR

Week number (1–53) within the calendar year

CALENDAR_DAY_OF_YEAR

Day number (1–366) within the calendar year

CALENDAR_DAY_OF_QUARTER

Day number within the quarter

CALENDAR_DAY_OF_MONTH

Day number within the month

CALENDAR_DAY_OF_WEEK

Day-of-week number; accepts index_by = 'DATE' (NLS territory mapping) or 'POSITION' (positional within week)

FISCAL_YEAR_NUMBER

Numeric fiscal year number for the input date (based on last day of fiscal year)

FISCAL_QUARTER_OF_YEAR

Quarter number within the fiscal year; accepts optional fis_year_start

FISCAL_MONTH_OF_YEAR

Month number within the fiscal year; accepts optional fis_year_start and index_by to choose date as opposed to positional numbering

FISCAL_MONTH_OF_QUARTER

Month number within the fiscal quarter; accepts optional fis_year_start.

FISCAL_WEEK_OF_YEAR

Week number within the fiscal year; accepts optional fis_year_start

FISCAL_DAY_OF_YEAR

Day number within the fiscal year; accepts optional fis_year_start

FISCAL_DAY_OF_QUARTER

Day number within the fiscal quarter; accepts optional fis_year_start

FISCAL_DAY_OF_MONTH

Day number within the fiscal month; accepts optional fis_year_start

FISCAL_DAY_OF_WEEK

Fiscal day-of-week; accepts optional fis_year_start and index_by.

RETAIL_YEAR_NUMBER

Numeric retail year number for the input date; accepts optional is_restated

RETAIL_QUARTER_OF_YEAR

Quarter number within the retail year; accepts optional is_restated

RETAIL_MONTH_OF_YEAR

Retail month number within the retail year; accepts optional is_restated and index_by

RETAIL_MONTH_OF_QUARTER

Retail month number within the retail quarter; accepts optional is_restated

RETAIL_WEEK_OF_YEAR

Retail week number within the retail year; accepts optional is_restated

RETAIL_WEEK_OF_QUARTER

Retail week number within the retail quarter; accepts optional is_restated

RETAIL_WEEK_OF_MONTH

Retail week number within the retail month; accepts optional is_restated

RETAIL_DAY_OF_YEAR

Day number within the retail year; accepts optional is_restated

RETAIL_DAY_OF_QUARTER

Day number within the retail quarter; accepts optional is_restated

RETAIL_DAY_OF_MONTH

Day number within the retail month; accepts optional is_restated

RETAIL_DAY_OF_WEEK

Retail day-of-week; accepts optional is_restated and index_by

Example 14-11

This example shows the CALENDAR_DAY_OF_WEEK with index_by parameter: 

SELECT   CALENDAR_DAY_OF_WEEK ( DATE ' 2025 - 01 - 01 ' , ' DATE ' ) dt ,
         CALENDAR_DAY_OF_WEEK ( DATE ' 2025 - 01 - 01 ' , 'POSITION' ) pos
FROM DUAL ;

Calendar

All of the Calendar X of Y functions are straightforward, with the exception of CALENDAR_DAY_OF_WEEK, which includes an optional index_by parameter. That parameter may be either 'POSITION' or 'DATE' (the default).

  • When set to 'DATE', the number corresponds to the day of the week the date falls on, based on the NLS_TERRITORY (such as, Sunday=1, Monday=2, and so on.).

  • When set to 'POSITION', it represents the positional index of the day within the week of the year.

For example, January 1, 2025 begins week 1 of 2025 and falls on a Wednesday:

SELECT   CALENDAR_DAY_OF_WEEK ( DATE '2025-01-01', 'DATE' ) dt ,
         CALENDAR_DAY_OF_WEEK ( DATE '2025-01-01', 'POSITION' ) pos
FROM DUAL ;

The following output is generated:

DT POS
-- ---
4   1

Fiscal

The index_by parameter is likewise included in FISCAL_DAY_OF_WEEK, but is also included in FISCAL_MONTH_OF_YEAR.

  • In this case, when 'DATE' is sued, the number corresponds to the month number of the associated fiscal month (such as, Jan=1, Feb=2, and so on.).

  • When 'POSITION' is used, it represents the month number from the beginning of the fiscal year.

For example:

SELECT   FISCAL_MONTH_OF_YEAR ( DATE '2025-07-04', DATE '2025-06-01', 'DATE' ) dt ,
         FISCAL_MONTH_OF_YEAR ( DATE '2025-07-04', DATE '2025-06-01', 'POSITION' ) pos
FROM DUAL ;

The following output is generated:

DT POS
-- ---
7   2

Retail

The retail functions also include the index_by parameter for RETAIL_MONTH_OF_YEAR and RETAIL_DAY_OF_WEEK.

Retail also includes 2 functions that are not defined for calendar or fiscal, namely RETAIL_WEEK_OF_QUARTER and RETAIL_WEEK_OF_MONTH. These are provided for retail because retail months are defined in terms of whole weeks (either 4 or 5), and by extension retail quarters are defined as 3 months, and thus also in terms of whole weeks.

14.1.5 Add X Periods Functions

Add X Periods functions are provided to allow navigation from one DATE to another across a particular grain (For example, CALENDAR_ADD_MONTHS, FISCAL_ADD_WEEKS, and RETAIL_ADD_YEARS). These functions take a NUMBER (integer) and add the given number of periods (such as years, months) to the given DATE, producing a new DATE. The time (hour/minute/second) components of the given DATE are preserved in the returned DATE. If the interval is negative, periods are subtracted.

Add X periods functions enable you to navigate forward or backward across periods (years, quarters, months, weeks, days). These functions preserve the time‑of‑day components of the input DATE.

  • num_periods is an integer; positive adds, negative subtracts.

  • Fiscal and retail variants follow their calendar rules (For example, fiscal months may start on non‑1st days; retail may skip days in restated calendars).

These functions are defined for calendar, fiscal, and retail periods.

See Also:

Calendar Functions in Oracle AI Database SQL Language Reference for syntactic and semantic information about Add X Periods functions

Table 14-6 Add X Periods Functions Summary

Function Description

CALENDAR_ADD_YEARS / CALENDAR_ADD_QUARTERS / CALENDAR_ADD_MONTHS / CALENDAR_ADD_WEEKS / CALENDAR_ADD_DAYS

Calendar period arithmetic; num_periods is integer;CALENDAR_ADD_MONTHS semantics match existing ADD_MONTHS for calendar

FISCAL_ADD_YEARS / FISCAL_ADD_QUARTERS / FISCAL_ADD_MONTHS / FISCAL_ADD_WEEKS / FISCAL_ADD_DAYS

Fiscal period arithmetic; accepts optional fis_year_start; month/day mapping follows fiscal month definitions (months may start on non‑1st).

RETAIL_ADD_YEARS / RETAIL_ADD_QUARTERS / RETAIL_ADD_MONTHS / RETAIL_ADD_WEEKS / RETAIL_ADD_DAYS

Retail period arithmetic; accepts optional is_restated; when is_restated='RESTATED', days not in the restated calendar are skipped

Calendar

The CALENDAR_ADD_MONTHS function is identical to the existing ADD_MONTHS function, and is provided for completeness. If the given date is the last day of its month or if the resulting month has fewer days than the day component of the given date, the last day of the resulting month is used. Otherwise, the same day within the resulting month is used.

Example 14-12 CALENDAR_ADD_MONTHS

This example shows CALENDAR_ADD_MONTHS behavior: 

SELECT   CALENDAR_ADD_MONTHS ( DATE ' 2025 - 02 - 28 ' , 1 ) feb28 ,
         CALENDAR_ADD_MONTHS ( DATE ' 2025 - 02 - 27 ' , 1 ) feb27 ,
         CALENDAR_ADD_MONTHS ( DATE ' 2025 - 01 - 30 ' , 1 ) jan30 ,
         CALENDAR_ADD_MONTHS ( DATE ' 2025 - 01 - 31 ' , 1 ) jan31
FROM DUAL ;

The following output is generated:


FEB28       FEB27       JAN30       JAN31
----------- ----------- ----------- -----------
31-MAR-2025 27-MAR-2025 28-FEB-2025 28-FEB-2025

Fiscal

Most of the fiscal functions are effectively equivalent to their calendar counterparts, though that is not always the case. When the fiscal year start is not on the 1st of the month, that completely changes the determination of when a day is the last day of the month.

Example 14-13

This example shows fiscal ADD_MONTHS differences when fiscal year starts on June 5: 

SELECT   CALENDAR_ADD_MONTHS ( DATE ' 2025 - 01 - 31 ' , 1 ) cal ,
         FISCAL_ADD_MONTHS ( DATE ' 2025 - 01 - 31 ' , 1 , DATE ' 2025 - 06 - 05 ' ) fis
FROM DUAL ;

The following output is generated:


CAL         FIS
----------- -----------
28-FEB-2025 03-MAR-2025

For the calendar, Jan 31 is the last day of the month, so the last day of the next month (Feb 28) is returned. For the fiscal calendar starting June 5, Jan 31 falls within a fiscal month running Jan 5–Feb 4. Jan 31 is the 27th day of its month, so the 27th day of the following month (Feb 5–Mar 4) is returned: Mar 3.

Example 14-14

This example shows FISCAL_ADD_MONTHS mapping for several days: 

SELECT FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 01 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb1 ,
       FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 02 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb2 ,
       FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 03 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb3 ,
       FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 04 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb4
FROM DUAL ;

The following output is generated:

FEB1        FEB2        FEB3        FEB4
----------- ----------- ----------- -----------
04-MAR-2025 04-MAR-2025 04-MAR-2025 04-MAR-2025

Retail

Retail functions are defined similarly, where RETAIL_ADD_MONTHS returns the corresponding day by position in its month, as with the fiscal example above. When is_restated is 'RESTATED', days not in the retail year are skipped.

Example 14-15

The last day of retail year 2022 is Jan 28, 2023. Because 2023 is a restated (53‑week) year, this example shows retail RETAIL_ADD_DAYS behavior with restated as apposed to not restated: 

SELECT   RETAIL_ADD_DAYS ( DATE ' 2023 - 01 - 28 ' , 1 , ' NOT RESTATED' ) not_res ,
         RETAIL_ADD_DAYS ( DATE ' 2023 - 01 - 28 ' , 1 , 'RESTATED' ) res
FROM DUAL ;

The following output is generated:

NOT_RES     RES
----------- -----------
29-JAN-2023 05-FEB-2023

In the non‑restated version, Jan 29 follows Jan 28 normally. In the restated version, Jan 29–Feb 4 don’t exist in the retail calendar, so the next valid day is Feb 5.

14.1.6 Since and Utility Functions

Since Function

CALENDAR_SINCE computes a human‑readable interval between a given date expression and SYSDATE (For example, "2 days ago"). The grain (For example, days, weeks, months) is automatically determined by the size of the interval.

Note:

Currently, only the Gregorian calendar is supported for CALENDAR_SINCE queries.

Example 14-16 CALENDAR_SINCE

This example shows CALENDAR_SINCE usage: 

SELECT   CALENDAR_SINCE ( DATE ' 2025 - 03 - 15 ' , 'YYYY' ) YEAR
FROM DUAL ;

Utility Function: RETAIL_DAY_EXISTS

RETAIL_DAY_EXISTS(dtexpr, is_restated) returns TRUE if the given date exists in the specified retail calendar variant. This function is useful to guard calls to retail functions when using restated calendars.

Example 14-17

This query ensures that only valid days in the restated retail calendar are included, preventing errors when dates fall outside the defined retail year:

SELECT RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month ,
       SUM ( sales ) sales
FROM fact
WHERE RETAIL_DAY_EXISTS ( order_date , 'RESTATED' ) = TRUE
GROUP BY ALL
ORDER BY 1 ;

14.1.7 Notes and Checklist

Key Rules and Constraints

  • Input types accepted: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or values implicitly convertible to DATE.

  • Format strings must include required specifiers and allow round‑trip conversion to DATE at the function’s grain; only 4‑digit year formats (YYYY or SYYYY) are allowed.

  • Fiscal parameter precedence: The fiscal start day must be ≤ 28; otherwise an error is raised. The function argument fis_year_start overrides session parameter CALENDAR_FISCAL_YEAR_START, which overrides system setting.

  • Retail calendar follows NRF 4‑5‑4 rules; is_restated controls restated behavior and RETAIL_DAY_EXISTS guards queries. Retail years normally have 52 weeks with a 53rd week added per the specified rule.

  • Redaction: FILTER WHERE translations and Oracle Data Redaction interact; V1 uses a redaction‑aware CASE expansion pattern to preserve correctness under Oracle Data Redaction.

  • Compatibility: database compatibility must be set to version 26; functions are usable in SQL executed inside PL/SQL blocks.

Quick Checklist Before Using a Function

  • Confirm input is DATE or convertible to DATE.

  • Choose calendar variant: calendar, fiscal, or retail.

  • If fiscal, decide whether to pass fis_year_start or rely on CALENDAR_FISCAL_YEAR_START.

  • If retail, decide is_restated and use RETAIL_DAY_EXISTS if needed.

  • Ensure format string contains required specifiers (For example, YYYY and Q for quarters).

  • Test around month ends, leap years, and retail 53‑week years.

  • Validate behavior under redaction if applicable.

14.2 Aggregation Filters

Aggregation filters allow developers to restrict the rows considered in aggregate functions such as SUM, COUNT, and AVG. This provides finer control over the data being aggregated without requiring additional subqueries.

Syntax

aggregate_function ( aggregate_function_arguments ) [ FILTER ( WHERE condition )]

  • aggregate_function: Any supported aggregate function (For example, SUM, COUNT, AVG, MAX, MIN).

  • aggregate_function_arguments: The column or expression to be aggregated.

  • condition: A Boolean predicate that determines which rows are included in the aggregation

Example 14-18 Counting only rows that meet a condition

This example demonstrates how to count only those rows where the status column equals 'ACTIVE'. 

SELECT COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_count
FROM employees;

Example 14-19 Summing values with a filter

This example shows how the sum of salary is calculated only for employees in the 'SALES' department. 

SELECT SUM(salary) FILTER (WHERE department = 'SALES') AS sales_total
FROM employees;

Example 14-20 Multiple filtered aggregates in one query

This example shows how multiple filtered aggregates can be combined in a single query to produce different conditional results.

SELECT
    COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_count,
    COUNT(*) FILTER (WHERE status = 'INACTIVE') AS inactive_count
FROM employees;

Example 14-21 Multiple filtered aggregates in one query

A query that wants to show total sales for the year, as well as the sales for the first two quarters of the year and the sales for the last two quarters of the year all in the same report can be written with the FILTER WHERE clause as follows:

SELECT
  year,
  SUM(sales) year_sales,
  SUM(sales) FILTER (WHERE qtr_num IN (1, 2)) q1q2_sales,
  SUM(sales) FILTER (WHERE qtr_num IN (3, 4)) q3q4_sales
FROM
  sales_fact f LEFT OUTER JOIN
    time_dim t ON (f.time_id = t.month_id)
GROUP BY year
ORDER BY year

Notes

  • Aggregation filters are evaluated after the WHERE clause of the query.

  • They provide a concise alternative to writing separate subqueries for each conditional aggregate.

  • Filters can be applied to any supported aggregate function.

See Also:

Aggregate Functions in Oracle AI Database SQL Language Reference for syntactic and semantic information about aggregation filters