求合同日期按月拆分Oracle脚本的等效T-SQL实现

等效T-SQL脚本 #

全版本兼容写法（支持SQL Server 2008及以上） #

-- 创建测试表
CREATE TABLE t 
(
    contract_no  VARCHAR(100),
    begin_date   DATE,
    end_date     DATE
);

-- 插入测试数据
INSERT INTO t VALUES ('AAA', CONVERT(DATE, '22-12-2020', 105), CONVERT(DATE, '13-05-2021', 105));
INSERT INTO t VALUES ('BBB', CONVERT(DATE, '20-03-2019', 105), CONVERT(DATE, '01-06-2019', 105));

-- 核心查询逻辑
WITH NumSeries AS (
    SELECT 1 AS l
    UNION ALL
    SELECT l + 1 FROM NumSeries WHERE l < 1000 -- 可根据实际最长合同月份差调整上限
)
SELECT 
    t.contract_no,
    t.begin_date,
    t.end_date,
    ns.l,
    EOMONTH(DATEADD(MONTH, ns.l - 1, DATEFROMPARTS(YEAR(t.begin_date), MONTH(t.begin_date), 1))) AS month_end_date
FROM t
CROSS APPLY (
    SELECT l FROM NumSeries 
    WHERE l <= ROUND(DATEDIFF(MONTH, 
        DATEFROMPARTS(YEAR(t.begin_date), MONTH(t.begin_date), 1),
        EOMONTH(t.end_date)
    ), 0) - 1
) ns
OPTION (MAXRECURSION 0); -- 取消递归层数限制，避免长序列报错

高版本简洁写法（支持SQL Server 2022及以上） #

利用SQL Server 2022新增的GENERATE_SERIES函数，写法更贴近原Oracle逻辑：

-- 创建测试表
CREATE TABLE t 
(
    contract_no  VARCHAR(100),
    begin_date   DATE,
    end_date     DATE
);

-- 插入测试数据
INSERT INTO t VALUES ('AAA', CONVERT(DATE, '22-12-2020', 105), CONVERT(DATE, '13-05-2021', 105));
INSERT INTO t VALUES ('BBB', CONVERT(DATE, '20-03-2019', 105), CONVERT(DATE, '01-06-2019', 105));

-- 核心查询逻辑
SELECT 
    t.contract_no,
    t.begin_date,
    t.end_date,
    gs.value AS l,
    EOMONTH(DATEADD(MONTH, gs.value - 1, DATEFROMPARTS(YEAR(t.begin_date), MONTH(t.begin_date), 1))) AS month_end_date
FROM t
CROSS APPLY GENERATE_SERIES(1, 
    ROUND(DATEDIFF(MONTH, 
        DATEFROMPARTS(YEAR(t.begin_date), MONTH(t.begin_date), 1),
        EOMONTH(t.end_date)
    ), 0) - 1
) gs;

功能说明 #

两种写法均和原Oracle脚本逻辑完全对齐，实现按合同起止月份拆分，输出每个中间月份的最后一天，输出结果和原Oracle执行结果完全一致。

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