Time until midnight between two date and time columns

Question

I need help with my query to calculate the time until midnight between two date and time columns break down by day

This is the main table:

ID	Start_Time	End_time	DateDiff
32221	01-01-2022 13:10:00	01-03-2022 13:10:00	2880

My query:

 
SELECT
    start_time.ID,
    start_time.Date_Time AS Start_time,
    end_time.Date_Time AS End_time,
    DATEDIFF(minute, start_time.Date_Time, end_time.Date_Time) AS DateDiff
FROM 
    Main 
 

what I need is similar to this:

ID	Date_start	End_time	DateDiff
32221	01-01-2022 13:10:00	01-01-2022 23:59:59	654
32221	01-02-2022 00:00:00	01-02-2022 23:59:59	1440
32221	01-03-2022 00:00:00	01-03-2022 13:10:00	781

how i can do that?

Answer 1

You can also solve this with a tally table, using the expanded (to show different cases) sample data

ID	StartTime	EndTime
32221	2022-01-01 13:10:00	2022-01-03 13:10:00
32222	2022-02-02 10:10:00	2022-02-02 17:10:00
32223	2022-03-03 19:10:00	2022-03-04 08:10:00
32224	2022-04-04 19:10:00	2022-04-08 08:10:00

and the code

with cteSampleData as ( --Enter some sample data, include spans of 0, 1, and >1 days
    SELECT * --Note that we need CONVERT to make sure the dates are treated as datetime not string!
    FROM (VALUES(32221, CONVERT(datetime2(0), '01-01-2022 13:10:00'), CONVERT(datetime2(0), '01-03-2022 13:10:00') )
        , (32222, '02-02-2022 10:10:00', '02-02-2022 17:10:00') 
        , (32223, '03-03-2022 19:10:00', '03-04-2022 08:10:00') 
        , (32224, '04-04-2022 19:10:00', '04-08-2022 08:10:00') 
    ) as Samp(ID, StartTime, EndTime)
), cteWithControl as ( --Add some fields to make testing cledarer - you could do this as part of a subsequent step instead
    SELECT * 
        , CONVERT(date, StartTime) as StartDate , CONVERT(date, EndTime) as EndDate
        , DATEDIFF(day, StartTime , EndTime) as DiffDays
        --, DATEDIFF(day, CONVERT(date, StartTime) , CONVERT(date, EndTime)) as DiffDays
    FROM cteSampleData
), cteTally as ( --Get a list of integers to represent days, assume nothing lasts longer than a year
    SELECT top 365 ROW_NUMBER() over (ORDER BY name) as Tally 
    FROM sys.objects --just a table we know has over 300 rows, look up tally tables for other generation methods
)--The real work begins below, partition the data into "same day" and "multi-day" spans
, cteSet as (
    SELECT ID, StartTime, EndTime, DiffDays, 1 as DayNumber
    FROM cteWithControl WHERE DiffDays = 0
    UNION ALL
    SELECT ID --For multi-day, cross with the tally table and treat first and last days special
        , CASE WHEN T.Tally = 1 THEN StartTime --For the first day the start time is the real time
            ELSE DATEADD (day, T.Tally - 1, startdate) END as StartTime --Otherwise it's the start of the day
        , CASE WHEN T.Tally = DiffDays + 1 THEN EndTime --For the last day the end is the real end
            ELSE DATEADD (second, -1, CONVERT(DATETIME2(0), DATEADD (day, T.Tally, startdate))) 
            END as EndTime --otherwise 1 second less than the next day
        , DiffDays, Tally as DayNumber
    FROM cteWithControl as D CROSS JOIN cteTally as T
    WHERE DiffDays > 0 AND T.Tally <= D.DiffDays + 1
)--Now we display the results and calculate the length (in minutes) of each span
SELECT *
    , DATEDIFF(MINUTE, StartTime, EndTime) as DateDiff 
FROM cteSet
ORDER BY ID, DayNumber 
 

we get the output

ID	StartTime	EndTime	DiffDays	DayNumber	DateDiff
32221	2022-01-01 13:10:00	2022-01-01 23:59:59	2	1	649
32221	2022-01-02 00:00:00	2022-01-02 23:59:59	2	2	1439
32221	2022-01-03 00:00:00	2022-01-03 13:10:00	2	3	790
32222	2022-02-02 10:10:00	2022-02-02 17:10:00	0	1	420
32223	2022-03-03 19:10:00	2022-03-03 23:59:59	1	1	289
32223	2022-03-04 00:00:00	2022-03-04 08:10:00	1	2	490
32224	2022-04-04 19:10:00	2022-04-04 23:59:59	4	1	289
32224	2022-04-05 00:00:00	2022-04-05 23:59:59	4	2	1439
32224	2022-04-06 00:00:00	2022-04-06 23:59:59	4	3	1439
32224	2022-04-07 00:00:00	2022-04-07 23:59:59	4	4	1439
32224	2022-04-08 00:00:00	2022-04-08 08:10:00	4	5	490

Answer 2

You may use a recursive CTE as the following:

With CTE As
(
  Select ID, Start_Time, End_time, DATEADD(Second, -1, DATEADD(Day, DATEDIFF(Day,0, Start_Time), 1)) et
  From main
  Union All
  Select C.ID, DATEADD(Second, 1, C.et), C.End_time, DATEADD(Day, 1, C.et)
  From CTE C Join main T
  On C.ID = T.ID
  Where DATEADD(Second, 1, C.et) <= C.End_time 
 )
Select ID, Start_Time, 
       Case When End_Time <= et Then End_Time Else et End As End_Time,
       DATEDIFF(Minute, Start_Time, DATEADD(Second, 1, Case When End_Time <= et Then End_Time Else et End)) As [DateDiff]
From CTE
Order By ID, Start_Time

See a demo with extended data sample from db<>fiddle.

Answer 3

You can loop through the times, always adding the time untill midnight, untill your 'start_time + 1 day' is bigger than your end_time.

The below code can be run directly in SQL (mind the date notation, my SQL is in united states notation, so if yours is in Europe this will give you back results for 3 months instead of 3 days);

DECLARE @start_time datetime2 = '01/01/2022 13:00:00';
DECLARE @end_time datetime2 = '03/01/2022 14:00:00';
DECLARE @daily_end_time datetime2=NULL;
DECLARE @Table Table (start_time datetime2, end_time datetime2, diff nvarchar(8));
DECLARE @diff_minutes_start int = DATEDIFF(MINUTE,@start_time,DateDiff(day,0,dateadd(day,1,@start_time)));
DECLARE @diff_minutes_end int = DATEDIFF(minute,@end_time,DateDiff(day,0,dateadd(day,1,@end_time)))
SET @daily_end_time = DATEADD(mi,@diff_minutes_start,@start_time)

WHILE @daily_end_time < @end_time
BEGIN
    INSERT INTO @Table (start_time,end_time,diff) 
    VALUES (
    @start_time,
    CASE WHEN DATEADD(day,1,@daily_end_time) > @end_time THEN @end_time ELSE 
@daily_end_time END,
CASE WHEN DATEADD(day,1,@daily_end_time) > @end_time THEN @diff_minutes_end ELSE 
@diff_minutes_start END )
  SET @daily_end_time = DATEADD(mi,@diff_minutes_start,@start_time)
SET @start_time = DATEADD(mi,1,@daily_end_time);
select @diff_minutes_start  = 


DATEDIFF(MINUTE,@start_time,DateDiff(day,0,dateadd(day,1,@start_time)));
    select @diff_minutes_end  = DATEDIFF(minute,@end_time,DateDiff(day,0,dateadd(day,1,@end_time)))
    END
    SELECT * FROM @Table

And the results: