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

Question

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?

Answer 1

You can add the seconds from datediff() to a date of 0 using dateadd(), and convert() that to time(0) data type like so:

select 
    StartTime
  , FinishTime
  , ElapsedTime = convert(time(0),dateadd(second,datediff(second,StartTime,FinishTime),0))
  /* for greater than 24 hours: */
  , ElapsedTime = right('0' + convert(varchar(9),(datediff(second,StartTime,FinishTime) / 3600 )),2) + ':' 
                + right('0' + convert(varchar(2),(datediff(second,StartTime,FinishTime) / 60) % 60 ),2) + ':' 
                + right('0' + convert(varchar(2),(datediff(second,StartTime,FinishTime) % 60 )),2)
from TimeLog

rextester demo: http://rextester.com/KVC7988

returns:

+-----------+------------+-------------+
| StartTime | FinishTime | ElapsedTime |
+-----------+------------+-------------+
| 08:00:00  | 08:25:00   | 00:25:00    |
| 08:25:00  | 09:15:00   | 00:50:00    |
| 09:55:00  | 12:32:00   | 02:37:00    |
| 12:32:00  | 14:31:00   | 01:59:00    |
| 12:32:00  | 13:55:00   | 01:23:00    |
| 09:55:00  | 11:42:00   | 01:47:00    |
| 07:30:00  | 08:45:00   | 01:15:00    |
| 07:00:00  | 07:15:00   | 00:15:00    |
| 07:15:00  | 08:10:00   | 00:55:00    |
+-----------+------------+-------------+

---

I believe that you may have been confused about how datediff() works. datediff(second,StartTime,FinishTime) returns number of seconds between the two times, it is not comparing the numbers in the 'seconds' place of each time.