How to get week number from date in SQL Server

by

In this comprehensive guide, I’ll walk you through multiple methods to get week numbers from dates in SQL Server, explain the nuances of different week numbering systems, and provide practical examples you can implement immediately.

How to get week number from date in SQL Server

Before diving into the code, it’s essential to understand that there are different standards for calculating week numbers.

  • ISO 8601 Standard: Weeks start on Monday, the first week of the year contains the first Thursday of the year
  • US Standard: Weeks typically begin on Sunday, the first week may contain January 1st
  • Fiscal Calendars: Many companies use custom fiscal week definitions

SQL Server provides functions to accommodate these different standards, but it’s crucial to know which one your business requires.

Approach 1: Using DATEPART() Function

The most straightforward method to get a week number in SQL Server is to use the DATEPART() function with the ‘wk’ or ‘week’ parameter.

SELECT DATEPART(wk, '2025-06-18') AS WeekNumber;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

How to get week number from date in SQL Server

This returns the week number according to SQL Server’s default settings, where:

  • Weeks start on Sunday (DATEFIRST = 7)
  • Week 1 is the week containing January 1st

For example, running this query today would return:

SELECT DATEPART(wk, GETDATE()) AS CurrentWeekNumber;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Get week number from date in SQL Server

The DATEPART function is versatile and widely used for date manipulation in SQL Server.

Customizing Week Start with SET DATEFIRST

SQL Server allows you to change which day is considered the first day of the week using the SET DATEFIRST command.

We can execute the following query to set Monday as the first day of the week.

SET DATEFIRST 1;
SELECT DATEPART(wk, '2025-06-18') AS WeekNumber;

We can execute the following query to set Sunday as the first day of the week (US standard).

SET DATEFIRST 7;
SELECT DATEPART(wk, '2025-06-18') AS WeekNumber;

The DATEFIRST parameter can be set to:

  • 1 = Monday
  • 2 = Tuesday
  • 3 = Wednesday
  • 4 = Thursday
  • 5 = Friday
  • 6 = Saturday
  • 7 = Sunday (US standard)

Keep in mind that SET DATEFIRST affects the entire session, so it’s important to reset it after use if needed.

Approach 2: Using ISO_WEEK for ISO 8601 Compliance

If you need to adhere to the ISO 8601 standard (commonly used in international contexts), SQL Server provides the ISO_WEEK parameter.

SELECT DATEPART(ISO_WEEK, '2025-06-18') AS ISOWeekNumber;

The ISO 8601 standard defines:

  • Weeks start on Monday
  • Week 1 is the first week with the majority of days in the new year (or the week containing January 4th)
  • A week can belong to the previous or next year

After executing the above query, I got the expected output as per the screenshot below.

week number from date in SQL Server

This can yield different results compared to the standard DATEPART(wk) function, particularly around year boundaries. For example, December 31, 2025, might fall under week 1 of 2026 according to ISO rules.

Approach 3: Creating a Custom Week Number Function

We can also create a custom function for this purpose.

CREATE FUNCTION dbo.GetCustomWeekNumber 
(
    @Date DATE, 
    @FirstDayOfWeek INT,  -- 1=Monday, 7=Sunday
    @FirstWeekType INT    -- 1=Jan1, 2=FirstFourDays, 3=FirstFullWeek
)
RETURNS INT
AS
BEGIN
    DECLARE @WeekNum INT;
    DECLARE @YearStart DATE = DATEFROMPARTS(YEAR(@Date), 1, 1);
    DECLARE @DayOfWeek INT = DATEPART(WEEKDAY, @YearStart);
    
    -- Adjust for first day of week
    SET @DayOfWeek = (@DayOfWeek - @FirstDayOfWeek + 7) % 7;
    
    DECLARE @DaysToAdd INT = 0;
    
    -- Calculate offset based on first week type
    IF @FirstWeekType = 2 -- First Four Days
    BEGIN
        IF @DayOfWeek > 3 
            SET @DaysToAdd = 7 - @DayOfWeek;
    END
    ELSE IF @FirstWeekType = 3 -- First Full Week
    BEGIN
        IF @DayOfWeek > 0
            SET @DaysToAdd = 7 - @DayOfWeek;
    END
    
    -- Calculate week number
    SET @WeekNum = DATEDIFF(DAY, DATEADD(DAY, @DaysToAdd, @YearStart), @Date) / 7 + 1;
    
    RETURN @WeekNum;
END;

After executing the above query, the function was created successfully as shown in the screenshot below.

how to get week number in sql server

You can call this function with your preferred parameters:

We can execute the query below to retrieve the week number with US settings (Sunday start, with January 1 being week 1).

SELECT dbo.GetCustomWeekNumber('2025-06-18', 7, 1) AS USWeekNumber;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

how to get week number from date in sql

We can execute the below query to get an ISO-like week number (Monday start, first week with the majority of days)

SELECT dbo.GetCustomWeekNumber('2025-06-18', 1, 2) AS ISOLikeWeekNumber;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

how to find week number from date in sql server

Performance Considerations

When working with week numbers in production environments, I’ve learned several important lessons about performance.

Pre-calculate Week Numbers for Date Dimensions

For data warehouses or reporting systems, I recommend pre-calculating week numbers in a date dimension table.

This approach significantly improves query performance by eliminating the need to calculate week numbers.

Avoid Function Calls in WHERE Clauses

Using functions like DATEPART in WHERE clauses can prevent SQL Server from using indexes efficiently. Instead, join a pre-calculated date dimension.

Conclusion

In this article, we discuss multiple methods for obtaining week numbers from dates in SQL Server, explain the nuances of different week numbering systems, and provide practical examples.

You may also like the following articles.