如何基于datediff将单行拆分为多行（支持datediff>1场景）

需求说明 #

需要将包含起始和结束时间的单行数据，按日期拆分为多行，规则如下：

• 起始日期行：从startdatetime到当日23:59:59
• 中间完整日期行：每天从00:00:00到23:59:59
• 结束日期行：从当日00:00:00到enddatetime

输入示例 #

输出示例 #

当前实现（仅支持无跨天/跨1天场景） #

SELECT        
    [whatever], [startdatetime] AS altstartdatetime, 
    CAST([startdatetime] AS time) AS altstarttime, 
    CASE 
        WHEN DATEDIFF(day, [startdatetime], [enddatetime]) > 0 
            THEN CAST(CONVERT(varchar, CONVERT(date, [startdatetime])) + ' 23:59:59' AS datetime) 
            ELSE [enddatetime] 
    END AS altenddatetime, 
    CASE 
        WHEN DATEDIFF(day, [startdatetime], [enddatetime]) > 0 
            THEN CAST('23:59:59' AS time) 
            ELSE CAST([enddatetime] AS time) 
    END AS altendtime
FROM 
    [somewhere]

UNION ALL

SELECT        
    [whatever], 
    CASE 
        WHEN DATEDIFF(day, [startdatetime], [enddatetime]) = 1 
            THEN CAST(CONVERT(varchar, CONVERT(date, enddatetime)) + ' 00:00:00' AS datetime) 
    END AS altstartdatetime, 
    CAST('00:00:00' AS time) AS altstarttime, [enddatetime] AS altenddatetime, 
    CAST([enddatetime] AS time) AS altendtime
FROM
    [somewhere]
WHERE        
    DATEDIFF(day, [startdatetime], [enddatetime]) = 1

扩展支持任意跨天场景的解决方案 #

使用**递归CTE（公共表表达式）**可以轻松处理任意天数的跨区间拆分，无需手动添加多个UNION分支：

WITH DateRangeCTE AS (
    -- 锚点成员：处理起始日期的部分
    SELECT
        [whatever],
        [startdatetime] AS altstartdatetime,
        -- 起始日期的结束时间：如果跨天则到当日23:59:59，否则用原enddatetime
        CASE 
            WHEN DATEDIFF(day, [startdatetime], [enddatetime]) > 0 
                THEN DATEADD(second, -1, DATEADD(day, 1, CONVERT(datetime, CONVERT(date, [startdatetime]))))
            ELSE [enddatetime]
        END AS altenddatetime,
        -- 记录当前处理的日期，用于递归终止
        CONVERT(date, [startdatetime]) AS CurrentDate,
        [enddatetime]
    FROM [somewhere]

    UNION ALL

    -- 递归成员：生成中间完整日期和结束日期的部分
    SELECT
        dr.whatever,
        -- 下一天的起始时间00:00:00
        DATEADD(day, 1, CONVERT(datetime, dr.CurrentDate)) AS altstartdatetime,
        -- 下一天的结束时间：如果是结束日期则用原enddatetime，否则到当日23:59:59
        CASE
            WHEN DATEADD(day, 1, dr.CurrentDate) = CONVERT(date, dr.enddatetime)
                THEN dr.enddatetime
            ELSE DATEADD(second, -1, DATEADD(day, 2, CONVERT(datetime, dr.CurrentDate)))
        END AS altenddatetime,
        -- 推进当前日期
        DATEADD(day, 1, dr.CurrentDate) AS CurrentDate,
        dr.enddatetime
    FROM DateRangeCTE dr
    -- 递归终止条件：当前日期小于结束日期
    WHERE dr.CurrentDate < CONVERT(date, dr.enddatetime)
)
SELECT
    whatever,
    altstartdatetime,
    altenddatetime,
    CAST(altstartdatetime AS time) AS altstarttime,
    CAST(altenddatetime AS time) AS altendtime
FROM DateRangeCTE
ORDER BY whatever, altstartdatetime;

代码说明 #

1. 锚点成员：初始化起始日期的记录，处理起始当天的时间区间。
2. 递归成员：逐天生成后续日期的记录，直到当前日期达到结束日期。
3. 终止条件：当CurrentDate等于enddatetime的日期时停止递归。
4. 用DATEADD和CONVERT函数替代字符串拼接，避免格式转换问题，更安全可靠。

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