如何用SQL获取上一年同周同日的日期？

你的核心需求是找到给定日期在上一年中同周数、同星期几的日期，原公式的问题在于错误地通过周数与星期几的简单数值差计算偏移，忽略了不同年份周起始规则、首周天数差异等问题，导致结果偏差。

方法一：基于SQL Server默认周规则（周日为周起始） #

以下代码适用于SQL Server默认的周计算逻辑（DATEPART(WEEK)以周日为周第一天，WEEKDAY中周日=1、周六=7）：

DECLARE @now DATE = '2023-03-03';

-- 获取当前日期的周数和星期几
DECLARE @week_num INT = DATEPART(WEEK, @now);
DECLARE @week_day INT = DATEPART(WEEKDAY, @now);

-- 计算上一年第N周的起始日
DECLARE @last_year_start DATE = DATEFROMPARTS(YEAR(@now)-1, 1, 1);
DECLARE @last_year_week_start DATE = DATEADD(DAY, (@week_num - 1)*7 - (DATEPART(WEEKDAY, @last_year_start) - 1), @last_year_start);

-- 偏移到对应星期几的日期
SELECT DATEADD(DAY, @week_day - 1, @last_year_week_start) AS TargetDate;

方法二：基于ISO周规则（周一为周起始，更通用） #

如果需要遵循国际标准的ISO周规则（周一为周第一天，周数按当年第一个周四所在周计算），可以使用以下代码，避免系统@@DATEFIRST设置的影响：

DECLARE @now DATE = '2023-03-03';

-- 获取ISO周数和ISO星期几（周一=1，周日=7）
DECLARE @iso_week_num INT = DATEPART(ISO_WEEK, @now);
DECLARE @iso_week_day INT = CASE DATEPART(WEEKDAY, @now) 
                            WHEN 1 THEN 7  -- 周日转为ISO的7
                            ELSE DATEPART(WEEKDAY, @now) - 1 
                            END;

-- 找到上一年对应ISO周的周一
DECLARE @last_year_first_week_anchor DATE = DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(@now), 1, 4));
DECLARE @last_year_iso_week_start DATE = DATEADD(WEEK, @iso_week_num - 1, @last_year_first_week_anchor);
SET @last_year_iso_week_start = DATEADD(DAY, 1 - CASE DATEPART(WEEKDAY, @last_year_iso_week_start) 
                                                  WHEN 1 THEN 7 
                                                  ELSE DATEPART(WEEKDAY, @last_year_iso_week_start) 
                                                  END, @last_year_iso_week_start);

-- 偏移到目标星期几
SELECT DATEADD(DAY, @iso_week_day - 1, @last_year_iso_week_start) AS TargetDate;

原公式错误原因 #

原公式通过(周数*7+星期几)的差值计算偏移，但该逻辑没有考虑：

• 不同年份的周起始日可能不同（比如上一年1月1日是周六，当年1月1日是周日，周数的起始偏移不同）
• 部分年份的第一周天数不足7天，直接用周数*7计算会导致日期偏移错误

内容的提问来源于stack exchange，提问作者khouloudbelhaj