You can use the T-SQL function DATEPART() to return the week number from a date in SQL Server.

By “week number” I mean the week’s number within the year of the specified date.

Example

To return the week number, use week as the first argument of the DATEPART() function.

DECLARE @date date = '2020-07-20';
SELECT DATEPART(week, @date);

Result:

30

Alternative Arguments

Alternatively, you can use wk or ww as the first argument to do the same thing.

DECLARE @date date = '2024-07-20';
SELECT 
    DATEPART(week, @date) AS week,
    DATEPART(wk, @date) AS wk,
    DATEPART(ww, @date) AS ww;

Result:

+--------+------+------+
 | week   | wk   | ww   |
 |--------+------+------|
 | 29     | 29   | 29   |
 +--------+------+------+  

You’ll notice that the week number in these results is different than the week number in the previous example, even though both examples used 20th July as their dates. This is to be expected.

The two examples use a different year. The week’s number can be affected by the year. So in other words, just because 20th July is the 30th week in one year, it doesn’t mean that it will be the 30th week every year. Just something to be mindful of.

ISO Week Number

You can also return the ISO week number from a date by using the iso_week argument. 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.