MSSQL中如何按工作时间（09:00-17:00）规则执行DATEADD MINUTES计算

你的需求核心是仅当起始时间处于09:00-17:00工作时段时，按照工作时间规则累加分钟数，而不是直接无限制累加。你原来的SQL语句只是过滤了起始时间的范围，但累加逻辑还是普通的DATEADD，自然得不到跨天的预期结果。

实现思路拆解 #

要实现这个需求，我们需要分情况处理：

1. 先判断起始时间是否在当天的工作时段内（09:00-17:00）；
2. 如果在工作时段内：
   • 计算起始时间到当天工作结束（17:00）的剩余分钟数；
   • 若要累加的分钟数≤剩余分钟数，直接正常累加即可；
   • 若要累加的分钟数超过剩余分钟数，先扣除当天剩余时长，再计算跨天的工作时长（每天按8小时/480分钟计算），最后将剩余时长加到下一个工作日的09:00之后；
3. 若起始时间不在工作时段内，按照普通逻辑累加分钟数。

完整SQL实现 #

你可以用CASE表达式结合日期函数来实现，这里以你的示例值为例：

DECLARE @StartDate DATETIME = '2018-05-24 15:00';
DECLARE @AddMinutes INT = 180;

SELECT 
    CASE
        -- 判断起始时间是否在当天工作时段内
        WHEN @StartDate BETWEEN CAST(CAST(@StartDate AS DATE) AS DATETIME) + '09:00:00' 
                           AND CAST(CAST(@StartDate AS DATE) AS DATETIME) + '17:00:00'
        THEN
            CASE
                -- 累加分钟数未超过当天剩余工作时间，直接累加
                WHEN @AddMinutes <= DATEDIFF(MINUTE, @StartDate, CAST(CAST(@StartDate AS DATE) AS DATETIME) + '17:00:00')
                THEN DATEADD(MINUTE, @AddMinutes, @StartDate)
                ELSE
                    -- 计算跨天需要处理的分钟数
                    DATEADD(MINUTE, 
                            -- 剩余需要累加的分钟数（扣除当天剩余工作时间后）
                            (@AddMinutes - DATEDIFF(MINUTE, @StartDate, CAST(CAST(@StartDate AS DATE) AS DATETIME) + '17:00:00')) % 480,
                            -- 计算基准日期：下一个工作日的09:00 + 完整工作日的天数
                            DATEADD(DAY, 
                                    1 + (@AddMinutes - DATEDIFF(MINUTE, @StartDate, CAST(CAST(@StartDate AS DATE) AS DATETIME) + '17:00:00')) / 480,
                                    CAST(CAST(@StartDate AS DATE) AS DATETIME) + '09:00:00'
                            )
                    )
            END
        -- 起始时间不在工作时段内，正常累加
        ELSE DATEADD(MINUTE, @AddMinutes, @StartDate)
    END AS CalculatedDateTime;

验证你的示例 #

执行上述SQL后，会得到预期结果2018-05-25 10:00:00.000：

• 当天剩余工作时间：17:00 - 15:00 = 120分钟；
• 需要跨天处理的分钟数：180 - 120 = 60分钟；
• 60分钟小于一天的工作时长（480分钟），所以直接加到第二天的09:00，得到10:00。

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