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

CodePudding user response：

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.

CodePudding user response：

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

db<>fiddle here