I have use multiple CTE
to show you the step by step calculation. It should be pretty easy to follow.
Basically it find the week_no
and day_no_of_week
for 2021-01-05
and then use that to find the same date for 2020
declare @input_date date = '2021-01-05',
@year_offset int = -1; -- previous year
with
cte1 as
(
select input_date = @input_date,
week_no = DATEPART(WEEK, @input_date),
first_day_of_week = DATEADD(WEEK, DATEDIFF(WEEK, 0, @input_date), 0)
),
cte2 as
(
select *,
day_no_of_week = DATEDIFF(DAY, first_day_of_week, @input_date) + 1
from cte1
),
cte3 as
(
select *,
first_day_of_the_prev_year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @input_date) + @year_offset, 0)
from cte2
),
cte4 as
(
select *,
first_day_of_week_prev_year = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, week_no - 1, first_day_of_the_prev_year)), 0)
from cte3
)
select *,
DATEADD(DAY, day_no_of_week - 1, first_day_of_week_prev_year) as the_required_date
from cte4
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…