Search code examples
sqlsql-servertimesum

SQL: Add multiple time(7) columns to get total duration

I have a table with five time(7) columns as below:

[Duration1] [time](7)
[Duration2] [time](7)
[Duration3] [time](7)
[Duration4] [time](7)
[TotalDuration] [time](7)

╔══════════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╗
║ Duration1        ║ Duration2        ║ Duration3        ║ Duration4        ║ TotalDuration    ║
╠══════════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╣
║ 00:07:10.0000000 ║ 00:15:47.0000000 ║ 00:00:14.0000000 ║ 00:13:31.0000000 ║ 00:00:00.0000000 ║
╚══════════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╝

How do I add Duration1, Duration2, Duration3 and Duration4 to get the total duration and update the TotalDuration column?

Solution

  • Would this work for you?

       --seconds
   DATEADD(ss,datepart("ss",Duration1) + datepart("ss",Duration2)  + datepart("ss",Duration3)  + datepart("ss",Duration4) ,
   --minutes
   DATEADD(mi, datepart("mi",Duration1) + datepart("mi",Duration2) + datepart("mi",Duration3) + datepart("mi",Duration4), 
   --hours
   DATEADD(hh, datepart("hh",Duration1) + datepart("hh",Duration2) + datepart("hh",Duration3) + datepart("hh",Duration4)
   --add to 00:00:00.0000000 as starting point
   , convert(time,'00:00:00.0000000'))))