CODEWITHSUNDEEP
sqlsql-servert-sqldate
Casey Crookston
Casey Crookston

Reputation: 13955

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

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!

Upvotes: 3

Views: 5143

Answers (3)

Elim Garak
Elim Garak

Reputation: 1817

Here is an example using DATEDIFF and DATEADD (line #3 is one line of code to get the value you are looking for). This may be similar to the accepted answer.

I am posting since this is a break down that I have used in a function when I documented for myself. 

--1. Get the number of Weeks since Monday, January 1, 1900
select DATEDIFF(wk, 0, '01/01/2016') -- 6052 Weeks

--2: Take the value since 1900 + Number of Weeks - 8 Days get you to Sunday.
select DATEADD(wk, 6052 + (31), -8);

--3. Put it all together..
select DATEADD(wk, DATEDIFF(wk, 0, '1/1/2016' ) + (31), -8); --=2016-07-24

Upvotes: 1

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

Look at my calculations. The idea is to take Jan 1st of the year and make arithmetic from there.

declare @year int=2016, @wk int=31
--A) Single chain calculations
select case datepart(weekday,cast(concat(@year,'-01-01') as date))
            when 1 then dateadd(wk,@wk-1,cast(concat(@year,'-01-01') as date))
            else dateadd(wk,@wk-1,
                  dateadd(day, 1/*8 if you want "first full week"*/ - datepart(weekday, cast(concat(@year,'-01-01') as date)),
                          cast(concat(@year,'-01-01') as date))) end

--B) the same in a better readable form    
;with tmp as (
select  cast(concat(@year,'-01-01') as date) jan01
)
select case datepart(weekday,jan01)
            when 1 then dateadd(wk, @wk-1, jan01)
            else dateadd(wk, @wk-1, dateadd(day, 1 - datepart(weekday, jan01), jan01)) end frstDay
from tmp

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415765

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

Upvotes: 3

Related Questions