Retailers compare sales week by week, so their calendar is made of whole weeks: the 4-5-4 calendar of the National Retail Federation gives each quarter months of four, five, and four weeks, and every month starts on the same weekday. The RETAIL_ functions of Oracle AI Database 26ai name and compute periods of that calendar, so a query can report by retail month or week without a calendar table.
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. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
retail_year(dtexpr [, is_restated [, fmt [, nlsparam]]]) -- and RETAIL_QUARTER, RETAIL_MONTH, RETAIL_WEEK, RETAIL_DAY, -- RETAIL_..._START_DATE, RETAIL_..._END_DATE, RETAIL_ADD_..., and -- RETAIL_DAY_EXISTS, RETAIL_WEEK_OF_MONTH, RETAIL_WEEK_OF_QUARTER
The family has the same members as the CALENDAR_ functions, plus a few of its own. Because 52 weeks are 364 days, some retail years have a 53rd week; is_restated chooses whether such a year is restated to compare with a 52-week year, and RETAIL_DAY_EXISTS tells whether a day exists in the restated year.
Retail Periods of a Date
The second query of this example places 15 March 2026 in the retail calendar (the first one shows fiscal periods).
Example:
-- a fiscal year that starts on 1 April
select fiscal_year(d, date '2026-04-01') as fiscal_year,
fiscal_quarter(d, date '2026-04-01') as fiscal_quarter,
fiscal_month_of_year(d, date '2026-04-01') as fiscal_month_no,
fiscal_year_start_date(d, date '2026-04-01') as fy_start
from (select date '2026-03-15' as d);
select retail_year(d) as retail_year, retail_month(d) as retail_month,
retail_week(d) as retail_week, retail_month_start_date(d) as month_start,
retail_month_end_date(d) as month_end
from (select date '2026-03-15' as d);Output:
FISCAL_YEAR FISCAL_QUARTER FISCAL_MONTH_NO FY_START ______________ _________________ __________________ ______________ FY2026 Q4-FY2026 3 01-APR-2025 RETAIL_YEAR RETAIL_MONTH RETAIL_WEEK MONTH_START MONTH_END ______________ _______________ ______________ ______________ ______________ RY2026 MAR-RY2026 W07-RY2026 01-MAR-2026 04-APR-2026
Retail March 2026 runs from 1 March to 4 April: five whole weeks, ending a few days after calendar March does. 15 March is in retail week 7 of retail year 2026.
Four, Five, and Four Weeks
The months of a retail quarter have four, five, and four weeks.
Example:
-- the retail months of the first quarter: 4, 5, and 4 weeks
select retail_month(d) as retail_month,
retail_month_start_date(d) as month_start, retail_month_end_date(d) as month_end,
(retail_month_end_date(d) - retail_month_start_date(d) + 1) / 7 as weeks
from (values (date '2026-02-15'), (date '2026-03-15'), (date '2026-04-15')) t (d);Output:
RETAIL_MONTH MONTH_START MONTH_END WEEKS _______________ ______________ ______________ ________ FEB-RY2026 01-FEB-2026 28-FEB-2026 4 MAR-RY2026 01-MAR-2026 04-APR-2026 5 APR-RY2026 05-APR-2026 02-MAY-2026 4
Each retail month ends on a Saturday and the next starts on a Sunday, so every month compares like for like with the same month a year earlier.
Things to Know
- A retail year starts near the beginning of February, so retail months do not line up with calendar months; use the start and end date functions rather than assuming.
- Group by retail_week(d) for week-over-week reports, and by retail_month(d) for monthly ones.
- Labels such as MAR-RY2026 and W07-RY2026 ignore NLS_DATE_FORMAT.
Related Guides
Conclusion
The RETAIL_ functions follow the 4-5-4 retail calendar of whole weeks: they name retail years, months, and weeks, return their start and end dates, and handle 53-week years with restatement. Use them to report retail sales by comparable periods without maintaining a calendar table.
