Fill missing dates given ranges of dates

Question

I have a table full of start and end dates like

Start	End
2023-05-19 20:00:00.000	2023-05-22 05:30:00.000
2023-05-22 05:30:00.000	2023-05-24 11:30:00.000

I would like one select query that returns all of them split into

Start	End
2023-05-19 20:00:00.000	2023-05-19 23:59:59.000
2023-05-20 00:00:00.000	2023-05-20 23:59:59.000
2023-05-21 00:00:00.000	2023-05-21 23:59:59.000
2023-05-22 00:00:00.000	2023-05-22 05:30:00.000

I've tried https://stackoverflow.com/a/57142843/22022398 But it does not work when it's more than 1 day

Start	End
2023-05-19 20:00:00.000	2023-05-19 23:59:59.000
2023-05-22 00:00:00.000	2023-05-22 05:30:00.000

My SQL server is MSSQL15 so I can't use generate_series()

I've also tried https://stackoverflow.com/a/25911919/22022398 but can't figure out how to implement it for a whole table instead of a single date

Are you sure you want to use `23:59:59.000` ideally you would use `00:00:00` ie the beginning of the next day and use exclusive end, see also https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common Otherwise you will miss one second of data — Charlieface, Jun 05 '23 at 10:26
@Charlieface One example I came across used 23:59:59.000 and I've just stuck with it. I'm okay with missing a second when transitioning to the next day. — Ashton, Jun 05 '23 at 10:54
*You* might be, but the business might not be. If you have sales data, for example, and you make a (nice big) sale at 23:59:59.123 I doubt management would be happy that it's excluded from *all* your figures due to it being *after* 23:59:59 but before 00:00:00. — Thom A, Jun 05 '23 at 11:35

score 2 · Answer 1 · answered Jun 05 '23 at 12:57

You can use a recursive query, that generates all and only your needed dates and then checks which are the ones to retain the original date values, and which are the ones to be updated with time '00:00:00' or '23:59:59', using window functions within case expressions.

WITH cte AS (
    SELECT ROW_NUMBER() OVER(ORDER BY Start_) AS rn,
           Start_                        AS firstdate, 
           Start_                        AS nextdate,
           End_                          AS lastdate
    FROM tab

    UNION ALL 

    SELECT rn, firstdate, DATEADD(dd, 1, nextdate) AS nextdate, lastdate
    FROM cte
    WHERE DATEADD(dd, 1, nextdate) < lastdate
)
SELECT rn, 
       CASE WHEN ROW_NUMBER() OVER(PARTITION BY rn ORDER BY nextdate) > 1
            THEN CAST(nextdate AS DATE)
            ELSE nextdate 
       END AS [Start],
       CASE WHEN ROW_NUMBER() OVER(PARTITION BY rn ORDER BY nextdate DESC) > 1
            THEN DATEADD(ss, -1, CAST(DATEADD(dd, 1, CAST(lastdate AS DATE)) AS DATETIME))
            ELSE lastdate
       END AS [End]
FROM cte
ORDER BY rn, [Start]

Output:

rn	Start	End
1	2023-05-19 20:00:00.000	2023-05-22 23:59:59.000
1	2023-05-20 00:00:00.000	2023-05-22 23:59:59.000
1	2023-05-21 00:00:00.000	2023-05-22 05:30:00.000
2	2023-05-22 05:30:00.000	2023-05-24 23:59:59.000
2	2023-05-23 00:00:00.000	2023-05-24 23:59:59.000
2	2023-05-24 00:00:00.000	2023-05-24 11:30:00.000

Check the demo here.

score 1 · Accepted Answer · answered Jun 05 '23 at 11:22

Something like this will do the trick. I would suggest using an actual numbers table instead of a CTE, but that is something you can tackle on your own.

CREATE TABLE #tmp(StartDT datetime, EndDT datetime)
INSERT INTO #tmp    VALUES
('2023-05-19 20:00:00.000', '2023-05-22 05:30:00.000'),
('2023-05-22 05:30:00.000', '2023-05-24 11:30:00.000')


;WITH NUMS AS
(
SELECT TOP 365
  ROW_NUMBER() OVER(ORDER BY c1.[object_id]) RN
FROM sys.all_columns c1
  CROSS JOIN sys.all_columns  c2
)
SELECT  
    CASE WHEN RN = 1 THEN StartDT  
        ELSE CAST(DATEADD(day, RN-1,CAST(StartDT as date) ) as datetime )
    END NewStart
    ,
    CASE WHEN RN = DATEDIFF(day, t1.StartDT,t1.EndDT)+1 
        THEN EndDT
    ELSE
        DATEADD(second,-1,  CAST(DATEADD(day, RN,CAST(StartDT as date)) as datetime))
    END EndDT

FROM #tmp t1
CROSS APPLY(SELECT RN from NUMS where RN <= DATEDIFF(day, t1.StartDT,t1.EndDT)+1) DayNums
ORDER BY NEWStart

score 0 · Answer 3 · answered Jun 05 '23 at 11:05

I think below code can help you :)

DECLARE @table AS TABLE (
                    startdate DATETIME,
                    enddate DATETIME
                    );
INSERT INTO @table
(
startdate,
enddate
)
VALUES
 ('2023-05-01', -- startdate - datetime
  '2023-05-05'  -- enddate - datetime
),
 ('2023-05-10', -- startdate - datetime
  '2023-05-15'  -- enddate - datetime
 );
WITH cte
 AS (
  SELECT
      startdate,
      DATEADD(SECOND, 86399, startdate) enddate,
      'N'                               AS msg,
      enddate                           AS newendate ---column to stop loop
 FROM @table
 UNION ALL
 SELECT
       DATEADD(SECOND, 1, enddate)                         startdate,
       DATEADD(SECOND, 86399, DATEADD(SECOND, 1, enddate)) AS enddate,
       'Y'                                                 msg,
       newendate
 FROM  cte
 WHERE startdate < cte.newendate
 )
 SELECT
     startdate,
     enddate
 FROM     cte
 ORDER BY 1;

score 0 · Answer 4 · answered Jun 05 '23 at 11:48

You can Use Cte


;WITH _list as (

        select 
                 min(Start_) mins
                ,max(Start_) as maxs

        from TestA
), listTest AS
(
    select 
            mins as StartTime,iif(
                                        mins=maxs
                                    ,maxs,
                           
                                        cast( cast( cast( mins as date) as varchar(100)) +cast( ' 23:59:59' as varchar(100)) as datetime)
                            
                                ) 
                                as EndTime
                                ,mins,maxs
    from _list
    UNION ALL 
    SELECT         DATEADD(DAY,1, 
                                cast( cast( cast(  StartTime as date) as varchar(100)) 
                               +cast( ' 00:00:00' as varchar(100)) as datetime)
                            
    
    
                    ) AS StartTime,
                    IIF( 
                            cast( DATEADD(DAY,1, StartTime ) as date)= 
                            cast(maxs as date)
                            ,maxs,DATEADD(DAY,1, EndTime )
                    ) AS EndTime
                     ,mins,maxs
    FROM   listTest 
    WHERE  StartTime < (select cast( maxs as date) from _list)

)
select  
            StartTime
           ,EndTime
from listTest a
left join TestA  b on cast( a.StartTime as date)=cast( b.Start_ as date)

Demo

Fill missing dates given ranges of dates

4 Answers4