Fiscal Calendars and ISO Weeks

Concepts covered: Date Parts, Date Truncation, Date Math, Grouping

This part of the question rarely announces itself. It arrives as "Q3 bookings", "last week's signups" or "revenue this year", and each phrase names a period whose edges the company chose. Microsoft's year begins in July, the US federal government's in October, and Apple's ends on the last Saturday of September. Before any DATE_TRUNC, the interviewer is listening for the question that fixes which calendar the business counts by.

4 calendars behind one word

Mapping a date to a month-offset year

For a year that begins on the first of a month, shift each date forward by the number of months from the fiscal start to the next January, then read the year and quarter off the shifted date. A July year needs 6 months: July lands on January, so fiscal Q1 lines up with calendar Q1 of the shifted value. Whole-month shifts are safe here because they always land in the right month even when the day gets clipped (August 31 becomes February 28), and only the month and year feed the period.

-- order_date is the order's local date, derived from order_ts
-- as the next section shows
-- Fiscal year begins July 1 and is named by the year it ends
SELECT region,
       EXTRACT(YEAR FROM order_date + INTERVAL '6' MONTH)    AS fiscal_year,
       EXTRACT(QUARTER FROM order_date + INTERVAL '6' MONTH) AS fiscal_quarter,
       DATE_TRUNC('quarter', order_date + INTERVAL '6' MONTH)
         - INTERVAL '6' MONTH                                 AS quarter_start,
       SUM(amount)                                            AS revenue
FROM orders
WHERE order_date >= DATE '2024-07-01'   -- FY2025 only
  AND order_date <  DATE '2025-07-01'
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3;

The quarter_start column answers the follow-up that usually comes next, "now give me fiscal quarter to date". Truncating the shifted date and shifting it back yields the real first day of the fiscal quarter, 2025-01-01 for anything in fiscal Q3, and that value works in a range filter like any other date. The animation below runs the period mapping on the 4 dates that decide most fiscal reports.

SELECT order_id, order_date,
       EXTRACT(YEAR FROM order_date + INTERVAL '6' MONTH) AS fiscal_year,
       EXTRACT(QUARTER FROM order_date + INTERVAL '6' MONTH) AS fiscal_quarter
FROM orders
ORDER BY order_date;

Order 1 on 2024-06-30 shifts to 2024-12-30 and closes FY2024 Q4; order 2, a day later, shifts to 2025-01-01 and opens FY2025 Q1. Order 3 on 2025-01-02 sits in calendar Q1 and fiscal Q3, which is the row a DATE_TRUNC('quarter', ...) answer files under the wrong quarter. Order 4 on 2025-06-30 is the last day of FY2025.

Week-based years need a lookup table

A 52/53-week year has boundaries that move. Apple's year ends on the last Saturday of September, so most years run 52 weeks and every fifth or sixth year runs 53, as fiscal 2023 did. No month shift reproduces that, and a 4-5-4 retail calendar adds week-based months on top. The answer is a calendar table keyed by date that carries fiscal_year plus fiscal_quarter and fiscal_week. Build it from the dates finance publishes and join it on the order's date. Say the split out loud: arithmetic for a month-aligned year, a lookup for anything week-based, because there the boundary is a published date rather than a rule.

ISO weeks and the year they belong to

ISO 8601 weeks run Monday to Sunday, and week 1 is the week holding the year's first Thursday. That rule lets the week-year slide away from the calendar year by up to 3 days at either end, and it gives some years 53 weeks, 2026 among them.

The usual bug comes from pairing the calendar year with the ISO week. EXTRACT(YEAR ...) next to an ISO week number turns December 30 and 31, 2024 into the key (2024, 1), the same key as January 1 to 7, 2024, so a weekly report adds 2 days of late-December revenue into a week from 12 months earlier. Pair the ISO week with the ISO year, or group by the Monday that starts each week and skip both numbers: a week-start date is unique per week, so it never collides as a join key or a chart label. Which weekday a given engine treats as the start is the part to confirm against the engine in front of you.

About this lesson

This section is part of the Date Arithmetic: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.