Time difference with hours, minutes and seconds in SQL Server

Question

I need to find time difference between two columns with hours, minutes and seconds.

These are two datetime columns in my table:

STOP_TIME                      Start_Time
------------------------------------------------------
2016-05-10 03:31:00.000        2016-05-10 02:25:34.000

I calculated second difference for stoptime and starttime. 3926 is the second difference.

I need to convert this to time format hh:mm:ss.

Answer 1

This should work for you -

DECLARE @STOP_TIME DATETIME = '2016-05-10 03:31:00.000',
        @Start_Time DATETIME = '2016-05-10 02:25:34.000'
SELECT 
     RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) / 3600 AS VARCHAR(2)),2) + ':'
   + RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) % 3600/60 AS VARCHAR(2)),2) + ':'
   + RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) % 60 AS VARCHAR(2)),2)

Answer 2

Sql server supports adding and subtracting on Datetime data type, so you can simply do something like this:

DECLARE @StartTime datetime = '2016-05-10 02:25:34.000',
        @EndTime datetime = '2016-05-10 03:31:00.000'

SELECT CAST(@EndTime - @StartTime as Time) As TimeDifference       

Result: 01:05:26

Note: As TT rightfully wrote in his comment, casting to time will only work if the difference between @EndTime and @StartTime is less then 24 hours. If you need to compare times that are further apart, you need to use one of the other solutions suggested.