SQL如何计算自定义业务月的起止日期？

我有一位客户需要一份包含多项月累计指标的自动化日报。通常我会用以下SQL代码获取自然月的起止日期：

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)  AS 'First calendar date of current month'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))  AS 'Last calendar date of current month'

执行结果如下：

First calendar date of current month
2023-09-01 00:00:00.000

Last calendar date of current month
2023-09-30 23:59:59.000

但客户的业务周期并非标准自然月：以上个月倒数第2个工作日为起始，本月倒数第3个工作日为结束。以2023年9月为例，日期范围是'2023-08-30 00:00:00.000' - '2023-09-27 23:59:59.000'。我查到的资料大多围绕用DATEPART统计月度工作日数，而我需要的是具体的datetime值，请问该如何实现这个需求？

核心思路是从目标月份的最后一天开始倒推，跳过周末（周六、周日）来定位指定的工作日。以下是基于SQL Server的具体实现：

1. 单独计算业务周期起始日期（上个月倒数第2个工作日） #

-- 先获取上个月的最后一天
DECLARE @LastMonthLastDay DATE = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
DECLARE @StartDate DATE = @LastMonthLastDay
DECLARE @SkipCount INT = 0

-- 倒推找到倒数第2个工作日
WHILE @SkipCount < 2
BEGIN
    -- 排除周日（DATEPART(dw)返回1）和周六（返回7），需根据你的SQL Server设置调整数值
    IF DATEPART(DW, @StartDate) NOT IN (1, 7)
    BEGIN
        SET @SkipCount += 1
        -- 还没找到第2个的话继续倒推
        IF @SkipCount < 2 SET @StartDate = DATEADD(DAY, -1, @StartDate)
    END
    ELSE
    BEGIN
        SET @StartDate = DATEADD(DAY, -1, @StartDate)
    END
END

-- 转换为datetime格式的起始时间（00:00:00）
SELECT CAST(@StartDate AS DATETIME) AS 'Business_Cycle_Start'

2. 单独计算业务周期结束日期（本月倒数第3个工作日） #

-- 先获取本月的最后一天
DECLARE @CurrentMonthLastDay DATE = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
DECLARE @EndDate DATE = @CurrentMonthLastDay
DECLARE @SkipCount INT = 0

-- 倒推找到倒数第3个工作日
WHILE @SkipCount < 3
BEGIN
    IF DATEPART(DW, @EndDate) NOT IN (1, 7)
    BEGIN
        SET @SkipCount += 1
        IF @SkipCount < 3 SET @EndDate = DATEADD(DAY, -1, @EndDate)
    END
    ELSE
    BEGIN
        SET @EndDate = DATEADD(DAY, -1, @EndDate)
    END
END

-- 转换为datetime格式的结束时间（23:59:59）
SELECT DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(@EndDate AS DATETIME))) AS 'Business_Cycle_End'

3. 一次性获取起止日期（无变量版） #

如果需要在单个查询中得到结果，可以用CTE简化：

WITH LastMonthEnd AS (
    SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS LastDay
),
StartCalc AS (
    SELECT
        CASE
            -- 上个月最后一天是周日，倒数第2个工作日是往前推2天（周五）
            WHEN DATEPART(DW, LastDay) = 1 THEN DATEADD(DAY, -2, LastDay)
            -- 上个月最后一天是周一，倒数第2个工作日是往前推3天（上周四）
            WHEN DATEPART(DW, LastDay) = 2 THEN DATEADD(DAY, -3, LastDay)
            -- 最后一天是周六，往前推2天；其他工作日往前推1天
            ELSE DATEADD(DAY, -IIF(DATEPART(DW, LastDay) = 7, 2, 1), LastDay)
        END AS StartDate
    FROM LastMonthEnd
),
CurrentMonthEnd AS (
    SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS LastDay
),
EndCalc AS (
    SELECT
        CASE
            -- 本月最后一天是周日，倒数第3个工作日是往前推4天（周三）
            WHEN DATEPART(DW, LastDay) = 1 THEN DATEADD(DAY, -4, LastDay)
            -- 本月最后一天是周一，倒数第3个工作日是往前推5天（上周二）
            WHEN DATEPART(DW, LastDay) = 2 THEN DATEADD(DAY, -5, LastDay)
            -- 本月最后一天是周二，倒数第3个工作日是往前推5天（上周三）
            WHEN DATEPART(DW, LastDay) = 3 THEN DATEADD(DAY, -5, LastDay)
            -- 最后一天是周五/周六，往前推3天+额外周末天数；其他工作日直接推3天
            ELSE DATEADD(DAY, -IIF(DATEPART(DW, LastDay) IN (6,7), 3 + (DATEPART(DW, LastDay)-5), 3), LastDay)
        END AS EndDate
    FROM CurrentMonthEnd
)
SELECT
    CAST(StartDate AS DATETIME) AS 'Business_Cycle_Start',
    DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(EndDate AS DATETIME))) AS 'Business_Cycle_End'
FROM StartCalc, EndCalc

关键注意事项 #

• 工作日判断：上述代码默认DATEPART(DW, 日期)返回1代表周日、7代表周六。如果你的SQL Server语言设置不同（比如1代表周一），需要调整IN (1,7)中的数值，可以通过SELECT DATEPART(DW, '2023-09-10')（已知当天是周日）确认返回值。
• 节假日处理：如果需要排除法定节假日，需额外维护一个节假日表，在判断工作日时加入AND 日期 NOT IN (SELECT 节假日日期 FROM 节假日表)的条件。

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