Determine the date range of a calendar week in SQL

Question

the date, for example, is '2021-12-13'

So I detect the week's number of that date: datepart(wk, '2021-12-13') --> 51

And I need to find the dates Monday and Sunday of that 51 week in last year

Answer 1

You can start with the following snippet

with
original as (select '2021-12-23' as d),
lastyear as (
  -- determine the same date last year
  select dateadd(yy, -1, d) as d
  from original
),
adjusted as (
  -- since we do not want the same date, but the same calendar week,
  -- we might need to add the difference between the two calendar weeks
  select dateadd(
    wk,
    datepart(wk, original.d)-datepart(wk, lastyear.d),
    lastyear.d) as d
  from lastyear, original
)
select
 -- finally determine the start and end of the week
 dateadd(dd, 2 - datepart(dw, adjusted.d), adjusted.d) as "start",
 dateadd(dd, 8 - datepart(dw, adjusted.d), adjusted.d) as "end",
from adjusted

However, there are some things to note:

1. The 2 and 8 and the final dateadd are required, when your SQL server starts the week on Sunday (i.e. datepart(dw, ...) returns 2 for monday). It would be most optimal to read out the server config within the query and use the appropriate offset instead of hard coding it
2. The adjusted CTE is a very explicit way of dealing with the fact that the same date last year may fall into another calender week. There are most certainly cheaper ways to achieve this, but they very likely also look a lot more complicated and less "obvious" what they are doing.
3. I only played around with SQL fiddle a bit, so this really needs to be tested for corner cases I might have missed.
4. One corner cases is that calendar week 53 sometimes means the last week in the year and sometimes it means the first week, and sometimes it may mean both. I am currently unable to fix that, because I don't know your intentions how to deal with that.
5. The correct datepart for ISO weeks is isowk instead of wk. You may want to use that, but then you have to think about how to use it. Because Microsoft SQL does not support isowk for dateadd.

Answer 2

The week and weekday numbers depend on the DATEFIRST setting.
But you can use the @@datefirst variable to correct the calculation, so that it's not affected by the DATEFIRST setting.

Monday

dateadd(day, 1-(@@datefirst-1)-datepart(weekday, '2021-12-13'), '2021-12-13')

Sunday

dateadd(day, 7-(@@datefirst-1)-datepart(weekday, '2021-12-13'), '2021-12-13')

Test

set datefirst 1;
select *
, datepart(week, date_column) as week
, datepart(weekday, date_column) as weekday
, @@datefirst as df
, datename(weekday, date_column) as weekday_name
, [monday] = dateadd(day, 1-(@@datefirst-1)-datepart(weekday, date_column), date_column)
, [sunday] = dateadd(day, 7-(@@datefirst-1)-datepart(weekday, date_column), date_column)
from (values 
  (cast('2021-12-13' as date)),
  ('2021-12-14'), 
  ('2021-12-19')
) as val(date_column);
GO

date_column	week	weekday	df	weekday_name	monday	sunday
2021-12-13	51	1	1	Monday	2021-12-13	2021-12-19
2021-12-14	51	2	1	Tuesday	2021-12-13	2021-12-19
2021-12-19	51	7	1	Sunday	2021-12-13	2021-12-19

db<>fiddle here

Or you could create a Calendar table and query that.

select cal_year, cal_df1_week
, min(cal_date) as begin_of_week
, max(cal_date) as end_of_week
from ref_calendar
where cal_year     = datepart(year, '2021-12-14')
  and cal_df1_week = datepart(week, '2021-12-14')
group by cal_year, cal_df1_week

The creation of the calendar table can be found here

Answer 3

Be careful because Sunday and Monday of week 51 in a given year are not necessarily back to back dates:

set datefirst 1;
select datepart(week, dt) as start_week, dt,
    datename(weekday, candidate) as candidate_day,
    datepart(week, candidate) as candidate_week,
    candidate
from (values
    (cast('2021-12-13' as date)), ('2020-12-13'), ('2019-12-13'),
         ('2018-12-13'), ('2017-12-13'), ('2016-12-13'),
         ('2015-12-13'), ('2014-12-13'), ('2013-12-13')
) as t(dt)
cross apply (values (-54), (-53), (-52), (-51), (-50)) as w(week_offset)
cross apply (values
   (dateadd(week, week_offset, dateadd(day, -datepart(weekday, dt), dt)))) as v1(sunday)
cross apply (values (sunday), (dateadd(day, 1, sunday))) as v2(candidate)
where datepart(week, candidate) = 51
order by dt desc, candidate;

This logic is searching backward in a range of weeks and then looking for the dates that match as week 51. You'll see that things bounce around quite a bit. I'm not sure this is what you expect.