Fill missing dates given ranges of dates

I have a table full of start and end dates like

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

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

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

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:

Check the demo here.

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

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;

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