ISO Week Numbers vs DATEPART(week): Grouping Sales by Week

The first days of January can belong to the previous reporting year. An ISO week follows its own calendar rules. Grouping by the calendar year and a week number mixes two definitions and breaks totals at the boundary.

Decide Whether the Business Uses the ISO Week

SQL Server exposes several definitions of a week. DATEPART(week) treats January 1 as part of week one and uses the session's first weekday. The standard starts weeks on Monday.

Its first week contains January 4, equivalently the year's first Thursday. These rules produce different answers around New Year. Neither definition should be chosen by accident.

I ask for the reporting calendar before writing the aggregation. A business with its own fiscal calendar needs an explicit calendar table. The ISO rule is useful when that is the agreed standard.

Don't label a session-dependent week number as international. A correct sales sum under the wrong week label is still a reporting error, even when the arithmetic passes.

Expose the Boundary Dates

Use dates on both sides of New Year. Testing only a date in June hides the problem. The first query compares calendar year, ordinary week, and the value under the standard.

Read each output as a label with a definition. The sample dates are inputs for inspection. They don't claim anything about the sales stored in your own system.

Save the existing DATEFIRST setting before changing it in a shared query window. Restore it afterward. That avoids surprising the next statement in the same session.

The code deliberately uses a Monday setting first. Rerun with Sunday to compare the ordinary result. DATEPART(ISO_WEEK) keeps its own Monday-based rule despite the session setting, making this comparison particularly useful.

In my run, December 31, 2020 was week 53 under both rules. January 1, 2021 came back as week 1 from DATEPART(week) but week 53 from DATEPART(ISO_WEEK). January 4, 2021 started week 1 under the standard.

DECLARE @SavedDateFirst int = @@DATEFIRST;
SET DATEFIRST 1;
SELECT d.SaleDate, YEAR(d.SaleDate) AS CalendarYear,
       DATEPART(week,d.SaleDate) AS SessionWeek,
       DATEPART(ISO_WEEK,d.SaleDate) AS IsoWeekNumber
FROM (VALUES (CONVERT(date,'20201231')),
             (CONVERT(date,'20210101')),
             (CONVERT(date,'20210104'))) AS d(SaleDate);
SET DATEFIRST @SavedDateFirst;

Test the Session Setting Explicitly

DATEFIRST accepts the first day used by weekday-related calculations. Connection defaults and language settings influence it. Two sessions can therefore disagree about the ordinary week label for the same date.

An application that assumes a particular default builds a hidden dependency into its reports. Show the setting with @@DATEFIRST when diagnosing a discrepancy between two screens.

I check that dependency before blaming the imported dates. The query below keeps the date constant and changes only the setting. Compare both output columns. Don't generalize from one date to the whole calendar.

The ordinary definition depends on a session choice. The ISO definition follows its own standard. That distinction belongs in the report specification.

January 3, 2021 was a Sunday. With DATEFIRST 7 the query returned week 2, and with DATEFIRST 1 it returned week 1. DATEPART(ISO_WEEK) returned 53 both times.

DECLARE @SavedDateFirst int = @@DATEFIRST;
DECLARE @Date date = '20210103';
SET DATEFIRST 7;
SELECT @@DATEFIRST AS FirstDaySetting,
       DATEPART(week,@Date) AS SessionWeek,
       DATEPART(ISO_WEEK,@Date) AS IsoWeekNumber;
SET DATEFIRST 1;
SELECT @@DATEFIRST AS FirstDaySetting,
       DATEPART(week,@Date) AS SessionWeek,
       DATEPART(ISO_WEEK,@Date) AS IsoWeekNumber;
SET DATEFIRST @SavedDateFirst;

Find the ISO Week Year From Its Thursday

The week number needs its matching year. YEAR(SaleDate) isn't enough because an early January date belongs to December's ISO year. A late December date can belong to the next one.

Find the Thursday of the date's ISO week, then take that Thursday's year. Thursday determines the year under the standard's first-week rule.

The expression below counts days from a known Monday and normalizes the remainder. The extra normalization supports dates before the anchor too. It finds Monday-based weekday position without relying on DATEFIRST.

Moving three days from Monday gives Thursday. This makes the year calculation independent of session language and weekday settings, an important property for stored reporting logic.

For January 1, 2021 the query returned 2020 as the year, which matches the week 53 label beside it.

SELECT d.SaleDate,
       YEAR(DATEADD(day,
           3 - ((DATEDIFF(day,CONVERT(date,'19000101'),d.SaleDate) % 7 + 7) % 7),
           d.SaleDate)) AS IsoYear,
       DATEPART(ISO_WEEK,d.SaleDate) AS IsoWeekNumber
FROM (VALUES (CONVERT(date,'20201231')),
             (CONVERT(date,'20210101')),
             (CONVERT(date,'20210104'))) AS d(SaleDate);

Group Sales by ISO Week and ISO Year

Group by the derived year and the week number together. Week one occurs every year, so grouping by the number alone combines unrelated periods. The example uses synthetic sales values and returns a total per matched pair.

Store the expression in a calendar table for repeated reporting. That keeps one agreed definition available to every application consuming the data.

The date used for grouping also needs a business meaning. A UTC transaction timestamp and a local store date can fall on different days. Convert timestamps into the reporting zone before assigning calendar labels.

Don't silently use the server's current local date for historical sales. The week calculation works only after the underlying date represents the business day you intend to count.

The sample returned two groups: 2020 week 53 with 30.0000 and 2021 week 1 with 30.0000. The December 31 and January 1 sales landed in the same week, as they should.

WITH Sales AS
(
    SELECT SaleDate, Amount
    FROM (VALUES (CONVERT(date,'20201231'),CONVERT(decimal(19,4),10)),
                 (CONVERT(date,'20210101'),CONVERT(decimal(19,4),20)),
                 (CONVERT(date,'20210104'),CONVERT(decimal(19,4),30))) AS s(SaleDate,Amount)
), Labeled AS
(
    SELECT YEAR(DATEADD(day,
               3 - ((DATEDIFF(day,CONVERT(date,'19000101'),SaleDate) % 7 + 7) % 7),
               SaleDate)) AS IsoYear,
           DATEPART(ISO_WEEK,SaleDate) AS IsoWeekNumber, Amount
    FROM Sales
)
SELECT IsoYear, IsoWeekNumber, SUM(Amount) AS TotalAmount
FROM Labeled
GROUP BY IsoYear, IsoWeekNumber
ORDER BY IsoYear, IsoWeekNumber;

Include Empty Weeks in the Report

An aggregation over sales returns only weeks containing rows. A dashboard showing a continuous trend needs a calendar-driven result instead. Join weekly totals to the calendar's distinct year-week pairs.

Fill missing totals according to the business rule. Zero sales and unavailable data are different states, so decide whether a missing source load should really appear as zero.

How does your report display a week without transactions? Ask that before users compare two periods. Also test years that contain a week 53.

Don't assume a fixed count when generating labels or validating exports. The calendar makes these boundaries visible. It is cheaper to agree on them once than to debug December in every reporting procedure separately.

Keep Filters on the Original Date

Filter the fact table with a direct date range before deriving its weekly labels. That helps an index on the transaction date. Applying the week calculation in the WHERE clause can force extra work.

A calendar join also provides a clear start and end date. Use those boundaries for extraction, then use the year-week pair for display and grouping.

Preserve the reporting standard in the column names and documentation. A name such as WeekNumber leaves too much unstated. Include the ISO year alongside it in exports and keys.

January will still surprise someone every year. Your SQL doesn't need to join that tradition. A complete calendar label lets the numbers remain attached to their actual reporting period.

Test the labeling rule before connecting it to the dashboard. Include the last days of December and the first full January week. Keep these cases with the report's validation queries. They protect the calendar contract when someone later simplifies the expression or replaces it with a familiar YEAR function.

Related reading on this blog: Weekday Logic That Works Under Any DATEFIRST Setting and A Walkthrough: DATETRUNC Function in SQL Server.

A weekly total is not a week number alone, it is a year and week under one calendar.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.