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!

How do you define the "first" day of the week? Some use Sunday, some use Monday. — alroc, Jul 27 '16 at 14:35
Good question. I meant to specify that. I think the default is Sunday, and that's what I'd like. — Casey Crookston, Jul 27 '16 at 14:36
Maybe a duplicate: https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server?rq=1 — jpw, Jul 27 '16 at 14:44
@jpw, I saw that one. But that question, like all that I've seen, assume you start with a date. I need to start with a number (31) and return a date. — Casey Crookston, Jul 27 '16 at 14:48
So what you really need is the date of the Sunday which is in a given week of the year ("first" is too malleable to be used in this context), right? — alroc, Jul 27 '16 at 14:49
USE THIS LINK http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server — JonWay, Jul 27 '16 at 14:52
@JonWay I saw that one. But that question, like all that I've seen, assume you start with a date. I need to start with a number (31) and return a date. — Casey Crookston, Jul 27 '16 at 15:02

Joel Coehoorn · Accepted Answer · 2016-07-27T14:56:43.657

3

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

edited Jul 27 '16 at 14:56

answered Jul 27 '16 at 14:49

Joel Coehoorn

399,467
113
570
794

Ok yeah, something is weird here. SELECT DATEPART(wk, '7/27/2016') returns 31. But, if I put 31 back into your query, I get 7/31/2016. Given a date, the first day of the week that date is in can not be after that date. – Casey Crookston Jul 27 '16 at 15:11
The problem is the first day of the year is not a sunday. How do you want to account for that? Do you want to show a day from last year? Should the result for week 1 be 2016-01-03 or 2015-12-27? And if you make a change for this, it will shift _everything_ forward, including week 52 (do you want to allow for a week 53?). – Joel Coehoorn Jul 27 '16 at 15:17
I see your point. What we have now is week 1 starting on Jan 3, which ignores Jan 1 and Jan 2. I need to include those dates. So, we better have week 1 starting at Dec 27, 2015. – Casey Crookston Jul 27 '16 at 15:20
Easy enough to add a -1 with the week num, but remember that this will now change the results for week 52 from your question, with a partial week 53 at the end of the year. I'd also want to test this in a year that does start on sunday. You may also need a case statement to not make the adjustment if Jan 1 is sunday. – Joel Coehoorn Jul 27 '16 at 15:22
Got it. We now have 53 weeks in a year. – Casey Crookston Jul 27 '16 at 15:24
1

Technically, in leap years you can have 54 weeks if the year starts on a Saturday (assuming Sunday is you first day of the week.) It's a corner case, but it bit me a few years back. – Data Masseur Jul 27 '16 at 19:29

score 1 · Answer 2 · answered Jul 27 '16 at 15:38

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

`concat` was introduced in SQL Server 2012. For earlier versions you can use `cast(@year as varchar)+'-01-01'`. Also CTE first appear in 2008. — Alex Kudryashev, Jul 27 '16 at 16:01

Elim Garak · Answer 3 · 2016-10-01T23:11:30.227

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

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

3 Answers3