Share via

Calculating Elapsed Time From Start & End Time

VDT-7677 171 Reputation points
2021-09-24T14:29:55.9+00:00

Hi,

Running SQL Server 2008 R2. I have the following sample table: 

CREATE TABLE [dbo].[Test]
    (
    [Test_ID] [int] NOT NULL,
    [Start_Time] [char](5) NULL,
    [End_Time] [char](5) NULL,
    [Elapsed_Time] [char](5) NULL,
    CONSTRAINT
        [PK_Test_Test_ID] PRIMARY KEY CLUSTERED ([Test_ID] ASC)
    WITH
        (
 PAD_INDEX = OFF,
 STATISTICS_NORECOMPUTE = OFF,
 IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON,
 ALLOW_PAGE_LOCKS = ON
 )
    ON [PRIMARY]
    )
ON [PRIMARY]
GO

Start and End Times are stored in 'hh:nn' format.

How could I use T-SQL to calculate the elapsed time between [Start_Time] and [End_Time], stored in 'hh:nn' format? [Elapsed_Time] will never exceed 24:00 but there may be occasions where [Start_Time] could be 23:45 and [End_Time] could be 00:05. Under these conditions [Elapsed_Time] would need to be 00:20.

Thanks in advance for any assistance!

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Answer accepted by question author
  1. Tom Cooper 8,496 Reputation points
    2021-09-24T15:02:13.377+00:00 
    ;With cte As
(Select TestID, Elapsed_Time, DateDiff(minute, Start_Time, End_Time) + Case When Start_Time > End_Time Then 24*60 Else 0 End As TimeInMinutes
From dbo.Test)
Update cte
Set Elapsed_Time = Convert(char(5), DateAdd(minute, TimeInMinutes, '19000101'), 8)
From cte;

    Tom

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Cooper 8,496 Reputation points
    2021-09-25T02:36:32.827+00:00

    Then you want to handle the case when TimeInMinutes = 0 as a special case, so 

    ;With cte As
(Select TestID, Elapsed_Time, DateDiff(minute, Start_Time, End_Time) + Case When Start_Time > End_Time Then 24*60 Else 0 End As TimeInMinutes
From dbo.Test)
Update cte
Set Elapsed_Time = Case When TimeInMinutes = 0 Then '24:00'
    Else Convert(char(5), DateAdd(minute, TimeInMinutes, '19000101'), 8) End;

    Tom

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Alex Bhatti 0 Reputation points
    2025-08-20T10:18:34.7733333+00:00

    You can do this by making the Time_of_Start as well as the End time into an appropriate time type, and then using the DATEDIFF. The main thing to do is take into account the time that passes midnight.

    Here's how you can accomplish this:

    SELECT Test_ID, Start_Time, End_Time, RIGHT('0' + CAST(DATEDIFF(MINUTE, CAST(Start_Time AS TIME), DATEADD(DAY, CASE WHEN End_Time < Start_Time THEN 1 ELSE 0 END, CAST(End_Time AS TIME)) ) / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(MINUTE, CAST(Start_Time AS TIME), DATEADD(DAY, CASE WHEN End_Time < Start_Time THEN 1 ELSE 0 END, CAST(End_Time AS TIME)) ) % 60 AS VARCHAR), 2) AS Elapsed_Time FROM Test;

    This technique ensures that, If Start_Time is 23:45 and End_Time is 00.05 it gives you the correct time 00:20.

    I typically recommend validating the results using the help of a calculation of time differences like a work time calculator, to double-check edges in the event of testing different start and ending time. This helps to avoid errors when calculations or sessions span over the night.
    Regards,

    Shozab

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.