Get the week start date and week end date from week number

Behind the Scenes: A Deep Dive into SQL Date Arithmetic

It's Monday, So I Must Declare Variables

DATEADD and DATEPART are the power behind SQL Server's ability to play with dates. See them in action with a “week number” example, ensuring your query sits well with @@DATEFIRST, SQL Server's week-start setting.

-- SQL Server considers Sunday as the first day of the week by default, let's set Monday as first
SET DATEFIRST 1; 


Using DATEPART and DATEADD for Precision Date Calculations

DATEPART retrieves a weekday as a number, helping us calculate days to add/subtract to align with week boundaries.

DATEADD shifts in time by adding or subtracting units (days, weeks, months, etc.) to a base date.

-- Let's vacation one week from now!
SELECT DATEADD(week, 1, GETDATE()) AS OneWeekFromNow;


Variable Dates are Better Than Static Ones!

Maximize the flexibility by employing parameters or variable dates:

DECLARE @Year INT, @WeekNumber INT;
SET @Year = 2023;        -- Sarah Connor might not like this year
SET @WeekNumber = 15; 


SQL's Got You Covered for Various DATEFIRST Settings

Ensure your script withstands different @@DATEFIRST values. As DJ Khaled would say: "Always test – that's the major key!"

Example: Week 15 Start and End Dates (ISO Week Number System)

Specify Week 15 of 2023 and watch as SQL Server nimbly fetches the start and end dates:

-- Who's up for a trip to Week 15?
DECLARE @Year INT = 2023, @WeekNumber INT = 15;
SELECT 
    DATEADD(day, 1 - DATEPART(weekday, DATEFROMPARTS(@Year, 1, 1)), 
    DATEADD(week, @WeekNumber - 1, DATEFROMPARTS(@Year, 1, 1))) AS StartDate;


No getting lost in time with concrete boundaries laid out for each week!

Tackling Non-standard Week Numbers and Leap Years

Leap Years Don't Have To Be a Leap of Faith!

Leap Years bring an extra day to the party. Test against a range of years (both leap and non-leap) to affirm the integrity of your solution. The code won't leap off a cliff, we promise!

Not All Week Number Systems are Born Equal

For non-standard week number systems, you'll need to adjust your calculations. Give your code a dose of notes and documentation to avoid surprising your successors or future self.

References