SQL Server 获取当前及历史 Billing Cycle Date Range

2026-10-24支付阿杰

结论

先确定 @CurrentDate 所在月份的有效账单日：

当月存在 @BillingDayOfMonth 指定的日期时，直接使用该日期。
当月不存在该日期时，例如 2 月 31 日，就使用当月最后一天。
如果 @CurrentDate 还没到当月账单日，当前周期从上个月的有效账单日开始。
周期结束日期是下个月有效账单日的前一天。

不能直接对上一周期的开始日期执行 DATEADD(MONTH, 1, CycleStartDate)。例如，2017-02-28 加一个月后是 2017-03-28，而正确的下一个账单日应该是 2017-03-31。因此，每个月的有效账单日都需要单独计算。

查询实现

以下查询会生成当前周期和此前 11 个周期，共 12 个周期：

DECLARE @CurrentDate DATE = '2018-01-02';
DECLARE @BillingDayOfMonth INT = 31;
DECLARE @CycleCount INT = 12;

IF @BillingDayOfMonth NOT BETWEEN 1 AND 31
    THROW 50001, '@BillingDayOfMonth must be between 1 and 31.', 1;

;WITH
Numbers AS
(
    SELECT 0 AS N

    UNION ALL

    SELECT N + 1
    FROM Numbers
    WHERE N + 1 < @CycleCount
),
CurrentMonth AS
(
    SELECT DATEFROMPARTS
    (
        YEAR(@CurrentDate),
        MONTH(@CurrentDate),
        1
    ) AS MonthStart
),
CurrentMonthBoundary AS
(
    SELECT
        DATEADD
        (
            DAY,
            CASE
                WHEN @BillingDayOfMonth > DAY(EOMONTH(MonthStart))
                    THEN DAY(EOMONTH(MonthStart))
                ELSE @BillingDayOfMonth
            END - 1,
            MonthStart
        ) AS BillingDate
    FROM CurrentMonth
),
CurrentCycle AS
(
    SELECT
        CASE
            WHEN @CurrentDate >= BillingDate
                THEN BillingDate
            ELSE
                DATEADD
                (
                    DAY,
                    CASE
                        WHEN @BillingDayOfMonth >
                             DAY(EOMONTH(DATEADD(MONTH, -1, BillingDate)))
                            THEN DAY(EOMONTH(DATEADD(MONTH, -1, BillingDate)))
                        ELSE @BillingDayOfMonth
                    END - 1,
                    DATEFROMPARTS
                    (
                        YEAR(DATEADD(MONTH, -1, BillingDate)),
                        MONTH(DATEADD(MONTH, -1, BillingDate)),
                        1
                    )
                )
        END AS CycleStartDate
    FROM CurrentMonthBoundary
),
CycleMonths AS
(
    SELECT
        N,
        DATEADD
        (
            MONTH,
            -N,
            DATEFROMPARTS
            (
                YEAR(CycleStartDate),
                MONTH(CycleStartDate),
                1
            )
        ) AS CycleMonth
    FROM Numbers
    CROSS JOIN CurrentCycle
),
Cycles AS
(
    SELECT
        N,
        DATEADD
        (
            DAY,
            CASE
                WHEN @BillingDayOfMonth > DAY(EOMONTH(CycleMonth))
                    THEN DAY(EOMONTH(CycleMonth))
                ELSE @BillingDayOfMonth
            END - 1,
            CycleMonth
        ) AS CycleStartDate,

        DATEADD
        (
            DAY,
            CASE
                WHEN @BillingDayOfMonth >
                     DAY(EOMONTH(DATEADD(MONTH, 1, CycleMonth)))
                    THEN DAY(EOMONTH(DATEADD(MONTH, 1, CycleMonth)))
                ELSE @BillingDayOfMonth
            END - 1,
            DATEADD(MONTH, 1, CycleMonth)
        ) AS NextCycleStartDate
    FROM CycleMonths
)
SELECT
    CycleStartDate,
    DATEADD(DAY, -1, NextCycleStartDate) AS CycleEndDate
FROM Cycles
ORDER BY N
OPTION (MAXRECURSION 0);

查询结果如下：

CycleStartDate  CycleEndDate
2017-12-31      2018-01-30
2017-11-30      2017-12-30
2017-10-31      2017-11-29
2017-09-30      2017-10-30
2017-08-31      2017-09-29
2017-07-31      2017-08-30
2017-06-30      2017-07-30
2017-05-31      2017-06-29
2017-04-30      2017-05-30
2017-03-31      2017-04-29
2017-02-28      2017-03-30
2017-01-31      2017-02-27

计算逻辑

以 @CurrentDate = '2018-01-02'、@BillingDayOfMonth = 31 为例：

2018 年 1 月有 31 日，所以当月账单日是 2018-01-31。
2018-01-02 还没到当月账单日，当前周期应从上个月开始。
2017 年 12 月的有效账单日是 2017-12-31。
下一个账单日是 2018-01-31，当前周期的结束日期就是前一天，即 2018-01-30。
继续向前推算时，每个月都要比较指定日期和月末日期，取其中较早的日期。

所以：

2017 年 11 月没有 31 日，周期从 2017-11-30 开始。
2017 年 2 月没有 31 日，周期从 2017-02-28 开始。
2017-02-28 所在周期的下一个账单日是 2017-03-31，因此结束日期是 2017-03-30。

注意事项

示例使用 DATEFROMPARTS 和 EOMONTH，适用于 SQL Server 2012 及以上版本。
@BillingDayOfMonth 的取值必须在 1 到 31 之间。
@CycleCount 用于控制返回的周期数。设为 12 时，会返回当前周期和此前 11 个周期。
如果要生成大量周期，建议用永久 Numbers/Tally 表替代递归 CTE，以获得更稳定的执行效率。
CycleEndDate 必须用下一个有效账单日减一天来计算。不能假定所有周期长度相同，也不能直接在当前周期开始日期上加一个月。