If you need to extract the ISO week number from a date in SQL Server, you can use the iso_week argument when calling the DATEPART() function. You can alternatively use the isowk or isoww arguments to do the same thing.

By “ISO week”, I’m referring to the ISO 8601 date and time standard.

ISO weeks start on Mondays and the first week of a year contains January 4 of that year. Therefore, it’s possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year.

This means that when you extract the week number from a date, you could get different results depending on whether you’re using the Gregorian calendar or the ISO 8601 date and time standard.

Example

DECLARE @date date = '2021-01-01';
SELECT DATEPART(iso_week, @date);

Result:

53

In this case, the date is January 1, 2021, but in ISO terms, it’s the 53rd week of 2020.

Comparison with Gregorian

Here’s another example to compare it to the Gregorian week.

DECLARE @date date = '2021-01-01';
SELECT 
  DATEPART(week, @date) AS week,
  DATEPART(iso_week, @date) AS iso_week;

Result:

+--------+------------+
 | week   | iso_week   |
 |--------+------------|
 | 1      | 53         |
 +--------+------------+ 

So we can see that the same date can have a different week number depending on whether we’re using the Gregorian calendar or the ISO standard.

We can do the same thing with a date near the end of the previous year.

DECLARE @date date = '2020-12-27';
SELECT 
  DATEPART(week, @date) AS week,
  DATEPART(iso_week, @date) AS iso_week;

Result:

+--------+------------+
 | week   | iso_week   |
 |--------+------------|
 | 53     | 52         |
 +--------+------------+ 

Alternative Arguments

As mentioned, can alternatively use the isowk or isoww to return the ISO week number.

DECLARE @date date = '2021-01-01';
SELECT 
    DATEPART(iso_week, @date) AS iso_week,
    DATEPART(isowk, @date) AS isowk,
    DATEPART(isoww, @date) AS isoww;

Result:

+------------+---------+---------+
 | iso_week   | isowk   | isoww   |
 |------------+---------+---------|
 | 53         | 53      | 53      |
 +------------+---------+---------+