SQL: How to subtract TIME data type from two different colums and produce sum AS in HH:MM:SS

Viewed 793

I have a table (TimeLog) that has 2 columns (StartTime, TIME(0), NOT NULL) and (FinishTime, TIME(0), NOT NULL) I want write a query that will subtract (FinishTime - StartTime) AS ElapsedTime and produce the sum in HH:MM:SS

I have tried

SELECT StartTime
      ,FinishTime
      ,REPLACE(CAST(CAST(DATEDIFF(Hour, StartTime, FinishTime )AS VARCHAR (2)) + ':' + CAST(DATEDIFF(MINUTE, StartTime, FinishTime )AS VARCHAR(2)) + ':'
+ Cast(DATEDIFF(Second, StartTime, FinishTime )AS VARCHAR (2)) AS VARCHAR(8)),'*','00') As ElapsedTime
FROM TimeLog
StartTime   FinishTime  ElapsedTime
08:00:00    08:25:00    0:25:00
08:25:00    09:15:00    1:50:00
09:55:00    12:32:00    3:00:00
12:32:00    14:31:00    2:00:00
12:32:00    13:55:00    1:83:00
09:55:00    11:42:00    2:00:00
07:30:00    08:45:00    1:75:00
07:00:00    07:15:00    0:15:00
07:15:00    08:10:00    1:55:00

But this will only work if ElapsedTime is below an hour. If more than 1 hour has elapsed the result is in incorrect format as I want. Can anyone assist?

1 Answers
Related