How to calculate difference between 2 times in SQL

Question

I have 2 columns in a table i.e. DutyHours(time(7)) and TimeSpentInOffice(time(7)).

How can I calculate the difference between these two times?

The datediff function returns in hour, minute, second or day etc but not in time.

Answer 1

DECLARE @null time;
SET @null = '00:00:00';

SELECT DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)

Reference: Time Difference

Edit: As per the comment, if the difference between the end time and the start time might be negative, then you need to use a case statement as such

SELECT CASE
           WHEN DATEDIFF(SECOND, End_Time, Start_Time) <=0
           THEN DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)
           WHEN DATEDIFF(SECOND, End_Time, Start_Time) >0
           THEN DATEADD(SECOND,  DATEDIFF(SECOND, End_Time, Start_Time), @null)
       END AS TimeDifference