3

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

Improve this question
3
  • 1
    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 sqlblog.org/2011/10/19/… Otherwise you will miss one second of data Commented Jun 5, 2023 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. Commented Jun 5, 2023 at 10:54
  • 1
    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. Commented Jun 5, 2023 at 11:35

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

Comments

1

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;
Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.