split row to multiple rows based on datediff

Question

I am struggling with splitting one row to multiple rows based on datediff between two columns. For e.g. W want to split row:

whatever	startdatetime	enddatetime
data1	2023-03-10 10:00:00	2023-03-14 15:00:00

into

whatever	altstartdatetime	altenddatetime
data1	2023-03-10 10:00:00	2023-03-10 23:59:59
data1	2023-03-11 00:00:00	2023-03-11 23:59:59
data1	2023-03-12 00:00:00	2023-03-12 23:59:59
data1	2023-03-13 00:00:00	2023-03-13 23:59:59
data1	2023-03-14 00:00:00	2023-03-14 15:00:00

Currently, I already achieved cases with no overlapping dates and 1 day overlapping with code:

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

How to include also cases with datediff > 1?

Answer 1

/* Using
https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
*/
/* DDL and Test Data */
CREATE TABLE #t
(
    whatever varchar(20) NOT NULL
    ,startdatetime datetime NOT NULL
    ,enddatetime datetime NOT NULL
);
GO
INSERT INTO #t
VALUES('data1', '20230310 10:00:00', '20230314 15:00:00');
GO
/* END DDL and Test Data */

SELECT T.whatever, T.startdatetime, T.enddatetime
    ,CASE
        WHEN X.[Value] > T.startdatetime
        THEN X.[Value]
        ELSE T.startdatetime
    END
    ,CASE
        WHEN DATEADD(day, DATEDIFF(day, 0, T.enddatetime), 0) = X.[Value]
        THEN T.enddatetime
        ELSE DATEADD(day, 1, X.[Value])
    END
FROM #t T
    CROSS APPLY dbo.DateRange(T.startdatetime, T.enddatetime, 'dd', 1) X1
    CROSS APPLY (VALUES(DATEADD(day, DATEDIFF(day, 0, X1.[Value]), 0))) X ([Value]);