How to get Monday, Sunday based on a Week Number in SQL Server?

Question

I have a week number (e.g. 23) and I'd like to get the dates for Monday and Sunday of that week.
I am assuming Monday as the first day of the week (e.g. 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 day 1 as Andomar said

Edit 2:

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

And week numbers vary as Andomar said

Cyberkiwi mentioned this code is wrong for 2007: it's wrong far more often than that. The same applies to his code too which matches 2007 but is equally wrong 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

We can use DATAFIRST variable to set the start day of the week. By default, it starts from Sunday(7). In order to start with Monday, we will set its value to 1.

# Printing default value
Select @@DATEFIRST; -- This will give 7 value i.e. default value

# Changing first day of the week to Monday.
Declare @CurrentWeekNumber int
SET DATEFIRST 1
set @CurrentWeekNumber= (SELECT DATEPART(WEEK, GETDATE()))
select @CurrentWeekNumber

PS: Changes to DATEFIRST are valid for current session i.e. it doesn't impact other DB query which might rely for different week start day.

Answer 4

A calendar table makes this kind of date query pretty simple. A calendar table just has the relevant calendar details calculated and stored at load time instead of calculated at run time. Depending on the application, that can speed things up a lot. This way 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 having a table of dates.

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 whether the names of the subfields isoyear, week, isodow are standard SQL.

Answer 5

Note: Any answer that starts with assuming year is 2011 might as well take a static first date, i.e. by replacing @firstMon in my last query below with just 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 - which returns a Mon/Sun of NULL when it doesn't exist in that week, such as 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's claim that this answer is wrong (within his answer), I submit below the proof of correctness

I have 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 through 2047. In EVERY SINGLE case, when the Monday/Sunday has been determined, and working backwards using DATEPART(week), SQL Server agrees with the week numbering from 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 around the 2005-2006 changeover

                                                 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 6

You might 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 FROM: 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
*/