Given a Week Number, Return the First Day of the Week in T-SQL

Question

This will give me a week number based on a date:

SELECT DATEPART(wk, '7/27/2016') AS [Week]

For example, that returns 31.

Now, what I need to do is find the first day of that week, and return it in a short date format. For example:

Given Week: 31
Return First Day of Week: July 24

Or

Given Week: 52
Return First Day of Week: Dec 25

I believe the default first day of the week is Sunday, and that's the date I need.

I've seen several posts here that come close, but none get me all the way there.

Thanks!

Answer 1

I helps to read this from the inside out. I added numbered comments to help.

declare @weekNum int;set @weeknum = 52;
select 
-- 3.  Add number of weeks
dateadd(wk, @weekNum, 
    --2.  first day of week 0 for that year (may belong to previous year)
    dateadd(ww, datediff(wk, 0, 
        --1.  First date of the year (week 0)
        dateadd(YEAR, datediff(year,0, getDate()),0)
     ),-1) -- -1 here because 1900-01-01 (date 0) was a Monday, and adding weeks to a Monday results in a Monday.
)

We can combine steps two and three, since they both add weeks:

declare @weekNum int;set @weeknum = 52;
select 
    --2.  first day of week 0 for that year (may belong to previous year) + number of weeks
    dateadd(ww, @weekNum + datediff(wk, 0, 
        --1.  First date of the year (week 0)
        dateadd(YEAR, datediff(year,0, getDate()),0)
     ),-1) -- -1 here because 1900-01-01 (day 0) was a Monday. Adding weeks to a Monday results in a Monday

Also, I think your example for week 31 is off by a week. You can see the full set for the year like this:

with weeks as 
(
    select top 52 row_number() over (order by  object_id) as wk  from sys.objects
)
select wk,
    --2.  first day of week 0 for that year (may belong to previous year) + number of weeks
    dateadd(ww, wk + datediff(wk, 0, 
        --1.  First date of the year (week 0)
        dateadd(YEAR, datediff(year,0, getDate()),0)
     ),-1) -- -1 here because 1900-01-01 (day 0) was a Monday. Adding weeks to a Monday results in a Monday
from weeks