如何在SQL Server 2016中用Transact SQL计算第45个工作日日期

问题分析 #

你当前的尝试逻辑存在错误：直接用DateAdd叠加工作日数的方式，没有考虑到中间的周末和节假日会额外占用日历天数，因此得到的结果会偏早。要找到第N个工作日，需要从起始日期开始，逐个日期校验是否为工作日（非周末+非节假日），直到累计的工作日数达到目标值。

解决方案：自定义函数实现 #

编写一个新的函数，从起始日期开始迭代判断，直到找到第N个工作日并返回该日期。

函数代码（起始日期不算入工作日计数） #

ALTER FUNCTION [dbo].[fncGetNthWorkingDay]
(
    @StartDate datetime,
    @NthDay int
)
RETURNS datetime
AS
BEGIN
    DECLARE @CurrentDate datetime = @StartDate
    DECLARE @WorkingDayCount int = 0

    -- 若目标天数为0或负数，直接返回起始日期
    IF @NthDay <= 0
        RETURN @StartDate

    WHILE @WorkingDayCount < @NthDay
    BEGIN
        -- 日期向后推进1天
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
        
        -- 判断当前日期是否为工作日：非周末且不在节假日表中
        IF DATENAME(WEEKDAY, @CurrentDate) NOT IN ('Saturday', 'Sunday')
           AND NOT EXISTS (
               SELECT 1 FROM ECD.dbo.HOLIDAYCALENDARTABLEDETAILS 
               WHERE HOLIDAYDATE = CAST(@CurrentDate AS DATE) AND HCSET=10642
           )
        BEGIN
            SET @WorkingDayCount = @WorkingDayCount + 1
        END
    END

    RETURN @CurrentDate
END

函数代码（起始日期算作第1个工作日） #

如果需要把起始日期本身计入工作日计数，调整函数逻辑如下：

ALTER FUNCTION [dbo].[fncGetNthWorkingDay]
(
    @StartDate datetime,
    @NthDay int
)
RETURNS datetime
AS
BEGIN
    DECLARE @CurrentDate datetime = @StartDate
    DECLARE @WorkingDayCount int = 0

    -- 先校验起始日期是否为工作日
    IF DATENAME(WEEKDAY, @CurrentDate) NOT IN ('Saturday', 'Sunday')
       AND NOT EXISTS (
           SELECT 1 FROM ECD.dbo.HOLIDAYCALENDARTABLEDETAILS 
           WHERE HOLIDAYDATE = CAST(@CurrentDate AS DATE) AND HCSET=10642
       )
    BEGIN
        SET @WorkingDayCount = 1
    END

    -- 若目标天数小于等于起始日的计数，直接返回起始日期
    IF @NthDay <= @WorkingDayCount
        RETURN @CurrentDate

    WHILE @WorkingDayCount < @NthDay
    BEGIN
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
        
        IF DATENAME(WEEKDAY, @CurrentDate) NOT IN ('Saturday', 'Sunday')
           AND NOT EXISTS (
               SELECT 1 FROM ECD.dbo.HOLIDAYCALENDARTABLEDETAILS 
               WHERE HOLIDAYDATE = CAST(@CurrentDate AS DATE) AND HCSET=10642
           )
        BEGIN
            SET @WorkingDayCount = @WorkingDayCount + 1
        END
    END

    RETURN @CurrentDate
END

使用示例 #

针对你的场景（起始日期2022-11-08，找第45个工作日），调用方式如下：

SELECT [dbo].[fncGetNthWorkingDay]('2022-11-08', 45) AS Day45Date

该函数会返回2023-01-17，符合预期结果。

性能优化建议 #

如果需要处理大范围日期查询，迭代方式效率可能不足，可以预先创建日期维度表：生成所有需要的日期，提前标记每个日期是否为工作日（非周末+非节假日），之后通过累加计数的方式快速定位第N个工作日，大幅提升查询效率。

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