When working with dates in SQL Server, you’ll quickly find that there are different ways to calculate the “week number” of a given date. Two of the most relevant systems are the ISO week (ISO 8601 standard) and what we’ll call the US week numbering system. If you’re working in a US environment, the latter is often the default approach (due to your session’s language setting), while ISO weeks are the international standard. These different week numbering systems will often produce different results, which can be confusing if you don’t know why.
Let’s walk through the difference, and then we’ll test it with some simple examples.
What is an ISO Week?
The ISO 8601 standard defines weeks as:
- A week starts on Monday.
- Week 1 is the first week with a majority (4 or more) of its days in January.
- It’s the week containing the first Thursday of the year.
- It’s the week containing January 4th.
- It has the year’s first working day in it (assuming that Saturdays, Sundays and 1 January are not working days).
This means the first few days of January can sometimes belong to the last week of the previous year, and the last few days of December can belong to the first week of the next year.
What is a US Week?
In SQL Server, the US-style week number goes like this:
- Week 1 is always the week containing January 1st, no matter what day of the week that falls on.
- The first day of the week depends on the session’s
DATEFIRSTsetting. In US English environments, the default is Sunday (DATEFIRST = 7), but other languages may default to Monday or another day. In any case, you can override this withSET DATEFIRST.
This means that US weeks are simpler than ISO weeks, but their behavior can shift depending on your session settings.
Example
Let’s create a small test table with some dates around the edge cases – late December and early January. These are the main spots where ISO and US numbering often diverge:
CREATE TABLE WeekTest (
SampleDate DATE
);
INSERT INTO WeekTest (SampleDate)
VALUES
('2024-12-29'), -- Sunday before New Year
('2024-12-30'), -- Monday
('2024-12-31'), -- Tuesday
('2025-01-01'), -- Wednesday (New Year’s Day)
('2025-01-02'), -- Thursday
('2025-01-05'), -- Sunday
('2025-01-06'); -- MondayNow let’s query both US and ISO week numbers side by side:
SELECT
SampleDate,
DATEPART(WEEK, SampleDate) AS US_Week,
DATEPART(ISO_WEEK, SampleDate) AS ISO_Week,
DATENAME(WEEKDAY, SampleDate) AS WeekdayName
FROM WeekTest
ORDER BY SampleDate;Output:
SampleDate US_Week ISO_Week WeekdayName
---------------- ----------- ----------- ------------------------------
2024-12-29 53 52 Sunday
2024-12-30 53 1 Monday
2024-12-31 53 1 Tuesday
2025-01-01 1 1 Wednesday
2025-01-02 1 1 Thursday
2025-01-05 2 1 Sunday
2025-01-06 2 2 Monday
Notice the main differences:
- December 29, 2024 (Sunday) is week 53 in the US system but week 52 in ISO.
- December 30–31, 2024 (Monday and Tuesday) are still week 53 in the US system, while ISO already calls them week 1 of 2025.
- January 1–2, 2025 line up as week 1 in both systems.
- January 5, 2025 (Sunday) is week 2 in the US system but still week 1 in ISO.
- January 6, 2025 (Monday) is week 2 in both systems, when they realign again.
This shows how the numbering can diverge right at the boundaries of the year.
Why This Matters
If you’re building reports, dashboards, or scheduling logic, the choice between ISO and US weeks can change how your data groups together. For example:
- If you need to align with international standards or compare across countries, ISO weeks are usually the right choice.
- If your organization is US-centric and doesn’t care about ISO standards, US weeks might be sufficient.
One of the main takeaways here is to know that DATEPART(WEEK, ...) and DATEPART(ISO_WEEK, ...) will not necessarily give the same result – especially around January 1.
Using SET DATEFIRST to Change the First Day of the Week
By default in SQL Server, the US-style DATEPART(WEEK, ...) assumes Sunday = 1 (meaning weeks start on Sunday). But you can override this with SET DATEFIRST. For example, if you set DATEFIRST 1, you’re telling SQL Server that Monday is the first day of the week.
Let’s select the language and DATEFIRST setting for the current session:
SELECT
@@LANGUAGE AS CurrentLanguage,
@@DATEFIRST AS CurrentDateFirst;Output:
CurrentLanguage CurrentDateFirst
--------------- ----------------
us_english 7
This tells us that our session uses the us_english language and our DATEFIRST is set to 7 (meaning that Sunday is the first day of the week). This is the default DATEFIRST setting for us_english.
Now let’s set Monday as the first day of the week:
SET DATEFIRST 1;
SELECT
@@LANGUAGE AS CurrentLanguage,
@@DATEFIRST AS CurrentDateFirst;Output:
CurrentLanguage CurrentDateFirst
--------------- ----------------
us_english 1
So while we’re still using us_english, we’ve explicitly changed the first day of the week to Monday instead of Sunday.
Now let’s run the above query again:
SELECT
SampleDate,
DATEPART(WEEK, SampleDate) AS US_Week_MondayFirst,
DATEPART(ISO_WEEK, SampleDate) AS ISO_Week,
DATENAME(WEEKDAY, SampleDate) AS WeekdayName
FROM WeekTest
ORDER BY SampleDate;Result:
SampleDate US_Week_MondayFirst ISO_Week WeekdayName
---------------- ------------------- ----------- ------------------------------
2024-12-29 52 52 Sunday
2024-12-30 53 1 Monday
2024-12-31 53 1 Tuesday
2025-01-01 1 1 Wednesday
2025-01-02 1 1 Thursday
2025-01-05 1 1 Sunday
2025-01-06 2 2 Monday
So, while changing DATEFIRST can affect the result (by changing the first day of the week), it doesn’t automatically mean that the results will always align with the ISO_Week results, especially around the end/start of the year.
So the main points to takeaway from this example are:
SET DATEFIRSTchanges where weeks start for US week numbering, but it still does not follow the ISO definition.- If you need true ISO compliance, always use
DATEPART(ISO_WEEK, ...). - If you just want your week to start on Monday but don’t care about ISO rules, you can use
SET DATEFIRST.