How to get Monday, Sunday by week number in SQL Server?

Question

I have a week number (e.g. 23) and I would like to get dates for Monday and Sunday of that week.
I accept Monday as the first day of the week (for example, I have SET DATEFIRST 1 in my script).

Answer 1

 DECLARE @startweek1 datetime SET DATEFIRST 1 --first monday of year SELECT @startweek1 = DATEADD(day, 8-DATEPART(weekday, '2011-01-01'), '2011-01-01') --day based SELECT DATEADD(day, 22 * 7, @startweek1) AS MondayWeek23, DATEADD(day, 23 * 7 -1 , @startweek1) AS SundayWeek23 --week based SELECT DATEADD(week, 22, @startweek1) AS MondayWeek23, DATEADD(day, -1, DATEADD(week, 23 , @startweek1)) AS SundayWeek23 

Edit:

This solution works if week 1 does not start on the first day, as Andomar said

Edit 2:

According to Wikipedia : 2008-12-29 is day 1 of week 1 of 2009 in ISO.

And the numbers of the week are changing , as Andomar said

Cyberkiwi mentioned that this code was incorrect in 2007: it makes mistakes much more often than that. The same applies to its code, which corresponds to 2007, but is equally false for the rest.

Answer 2

 Declare @StartDate datetime; Set @StartDate = '20110101'; With StartOfWeek As ( Select DateAdd( week , 23 , DateAdd( d , -(DatePart(dw, @StartDate) - 1) , @StartDate ) ) As Sunday ) Select Sunday, DateAdd(d,1,Sunday) As Monday From StartOfWeek 

Answer 3

Note. . Any answer starting with the expected year can also take a static first date, i.e. replacing @firstMon in my last request below with only the static date "20101227".

 SET DATEFIRST 1 declare @targetYear int declare @targetWeek int select @targetYear = 2011, @targetWeek = 23 declare @firstMon datetime set @firstMon = dateadd(d,1-datepart(dw, right(@targetYear,4)+'0101'),right(@targetYear,4)+'0101') select MonOfTargetWeek = dateadd(week, @targetWeek-1, @firstMon), SunOfTargetWeek = dateadd(week, @targetWeek-1, @firstMon+6) 

And the real final query that Mon / Sun returns NULL when it doesn't exist this week, like Mon of partial-week-one or Sun of partial-week-53

 ;with tmp(Mon, Sun) as ( select MonOfTargetWeek = dateadd(week, @targetWeek-1, @firstMon), SunOfTargetWeek = dateadd(week, @targetWeek-1, @firstMon+6) ) select RealMonday = case when Year(Mon) =@targetYear then Mon end, RealSunday = case when Year(Sun) =@targetYear then Sun end from tmp 

For GBN they claim that this answer is incorrect (in his answer), below I confirm the correctness

I turned the code into a function.

 CREATE function dbo.getMonSunForWeek(@targetYear int, @targetWeek int) returns table as return with pre(firstMon) as ( select dateadd(d,1-datepart(dw, right(@targetYear,4)+'0101'),right(@targetYear,4)+'0101')) , tmp(Mon, Sun) as ( select dateadd(week, @targetWeek-1, firstMon), dateadd(week, @targetWeek-1, firstMon+6) from pre ) select Mon, Sun, RealMonday = case when Year(Mon) =@targetYear then Mon end, RealSunday = case when Year(Sun) =@targetYear then Sun end from tmp GO 

And below, I present the ENTIRE range of years and weeks (1-53) for the years from 1950 to 2047. In the case of EVERY ONE , when Monday / Sunday was defined, and works in reverse with DATEPART(week) , SQL Server agrees with the numbering weeks using the function.

 set datefirst 1; select [Year] = years.number, [Week] = weeks.number, [Mon] = fun.realmonday, [Sun] = fun.realsunday, [WeekX] = isnull(datepart(week, fun.realmonday), datepart(week, fun.realsunday)), [MonX] = fun.Mon, [SunX] = fun.Sun from master..spt_values years inner join master..spt_values weeks on weeks.type='P' and weeks.number between 1 and 53 cross apply dbo.getMonSunForWeek(years.number, weeks.number) fun where years.type='P' and years.number between 1950 and 2047 order by [year], [Week] 

Output information on the transition for 2005-2006

  when including prior/next year Year Week Mon-of-week Sun-of-week datepart(week) Mon -and- Sun 2005 52 2005-12-19 2005-12-25 52 2005-12-19 2005-12-25 2005 53 2005-12-26 NULL 53 2005-12-26 2006-01-01 2006 1 NULL 2006-01-01 1 2005-12-26 2006-01-01 2006 2 2006-01-02 2006-01-08 2 2006-01-02 2006-01-08 

Answer 4

A calendar table makes this date query quite simple. The calendar table only has the corresponding calendar data, calculated and saved at boot time, and not calculated at run time. Depending on the application, this may speed things up. This method is dbms agnostic; it just uses literals in the WHERE clause.

 select cal_date from calendar where iso_year = 2011 and iso_week = 23 and cal_dow in ('Mon', 'Sun'); 

And another way that relies only on a date table.

 select cal_date from calendar where extract(isoyear from cal_date) = 2011 and extract(week from cal_date) = 23 and (extract(isodow from cal_date) = 1 or extract(isodow from cal_date) = 7); 

EXTRACT () is standard SQL, but I'm not sure if the subfield names isoyear, week, isodow are standard SQL.

Answer 5

 Declare @CurrentWeekNumber int SET DATEFIRST 1 set @CurrentWeekNumber= (SELECT DATEPART(WEEK, GETDATE())) select @CurrentWeekNumber 

Answer 6

You can try some dateadd logic ...

 dateadd(week, 23, '2011-01-01') dateadd(day, 7, dateadd(week, 23, '2011-01-01')) 

UPDATE:

 select dateadd(day, 23, dateadd(week, 23, '2011-01-01')) , Datename(weekday,dateadd(day, 23, dateadd(week, 23, '2011-01-01'))); // RETURNS 7/4/2011, Monday select Datename(weekday,dateadd(day, 23, dateadd(week, 23, '2011-01-01'))); // RETURNS Monday 

EXAMPLE: http://msdn.microsoft.com/en-us/library/ms186819.aspx

 DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111' SELECT 'year', DATEADD(year,1,@datetime2) UNION ALL SELECT 'quarter',DATEADD(quarter,1,@datetime2) UNION ALL SELECT 'month',DATEADD(month,1,@datetime2) UNION ALL SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2) UNION ALL SELECT 'day',DATEADD(day,1,@datetime2) UNION ALL SELECT 'week',DATEADD(week,1,@datetime2) UNION ALL SELECT 'weekday',DATEADD(weekday,1,@datetime2) UNION ALL SELECT 'hour',DATEADD(hour,1,@datetime2) UNION ALL SELECT 'minute',DATEADD(minute,1,@datetime2) UNION ALL SELECT 'second',DATEADD(second,1,@datetime2) UNION ALL SELECT 'millisecond',DATEADD(millisecond,1,@datetime2) UNION ALL SELECT 'microsecond',DATEADD(microsecond,1,@datetime2) UNION ALL SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2); /* Year 2008-01-01 13:10:10.1111111 quarter 2007-04-01 13:10:10.1111111 month 2007-02-01 13:10:10.1111111 dayofyear 2007-01-02 13:10:10.1111111 day 2007-01-02 13:10:10.1111111 week 2007-01-08 13:10:10.1111111 weekday 2007-01-02 13:10:10.1111111 hour 2007-01-01 14:10:10.1111111 minute 2007-01-01 13:11:10.1111111 second 2007-01-01 13:10:11.1111111 millisecond 2007-01-01 13:10:10.1121111 microsecond 2007-01-01 13:10:10.1111121 nanosecond 2007-01-01 13:10:10.1111111 */