SQL Server动态调整DATEADD增量 实现跨周末节假日工作日查询

现有每日中午执行的SQL Server查询，用于检索前序工作日至当日录入的记录，示例数据结构如下：

原有查询代码：

DECLARE @ReportDate = 6/21/2022
SELECT *
FROM SampleTable
WHERE CreatedDate BETWEEN DATEADD(D,-1,@ReportDate) AND GETDATE()

上述代码周二至周六运行可正常返回结果，但周一或节假日后首个工作日运行会出现查询范围偏差。需要将DATEADD的增量参数改为动态值，自动跳过周日及节假日（业务规则明确周六属于工作日），例如报表日期为6月21日周二时，增量值应为-3，以下是可落地的实现方案。

前置准备 #

法定节假日、调休放假日期无法通过内置日期函数自动识别，必须先维护一张独立的节假日配置表，提前录入所有需要跳过的非周日放假日期：

CREATE TABLE HolidayConfig (
    HolidayDate DATE PRIMARY KEY,
    HolidayRemark NVARCHAR(100) NULL -- 可选，存储节假日名称方便后续维护
)

注意：不需要录入周日日期，周日会通过日期逻辑自动识别跳过。

动态偏移量计算逻辑 #

核心规则：从报表日期开始逐天往前回溯，遇到周日或者命中节假日表的日期就继续向前，直到找到第一个符合规则的工作日，两个日期的差值就是DATEADD需要的动态偏移量。

循环实现（逻辑直观易维护） #

-- 修正原变量赋值的语法问题：日期值需要加引号，否则会被识别为数值除法运算
DECLARE @ReportDate DATE = '2022-06-21'
DECLARE @StartDate DATE = @ReportDate
DECLARE @Offset INT = 0

-- 回溯查找上一个有效工作日
WHILE 1 = 1
BEGIN
    SET @StartDate = DATEADD(DAY, -1, @StartDate)
    SET @Offset = @Offset + 1
    -- 工作日判断：非周日 + 不在节假日列表中
    -- 用DATENAME判断周日不受服务器DATEFIRST周起始设置影响，兼容性更好
    IF DATENAME(WEEKDAY, @StartDate) != 'Sunday'
       AND NOT EXISTS (SELECT 1 FROM HolidayConfig WHERE HolidayDate = @StartDate)
    BEGIN
        BREAK
    END
END

-- 最终业务查询
SELECT *
FROM SampleTable
WHERE CreatedDate BETWEEN @StartDate AND GETDATE()

无循环实现（性能更优，适合高频查询场景） #

基于数字序列表实现，避免循环开销，默认支持最长往前回溯30天，足够覆盖春节、国庆等连休场景：

DECLARE @ReportDate DATE = '2022-06-21'
DECLARE @StartDate DATE
DECLARE @Offset INT

;WITH NumSeries AS (
    SELECT TOP 30 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Step
    FROM sys.all_columns
)
SELECT TOP 1 @StartDate = DATEADD(DAY, -Step, @ReportDate), @Offset = Step
FROM NumSeries
WHERE DATENAME(WEEKDAY, DATEADD(DAY, -Step, @ReportDate)) != 'Sunday'
  AND NOT EXISTS (SELECT 1 FROM HolidayConfig WHERE HolidayDate = DATEADD(DAY, -Step, @ReportDate))
ORDER BY Step ASC

-- 最终业务查询
SELECT *
FROM SampleTable
WHERE CreatedDate BETWEEN @StartDate AND GETDATE()

验证说明 #

以6月21日周二为例，如果6月20日周一为法定节假日，回溯逻辑会依次校验：

• 往前1天：6月20日（周一，节假日，跳过）
• 往前2天：6月19日（周日，跳过）
• 往前3天：6月18日（周六，工作日，命中）
  最终偏移量为-3，完全符合业务要求。

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