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?