Search code examples
sqldatesumsql-date-functions

How to sum multiple hour : mins in sql?

i need to sum this varchar values.

time 1 = '13:06' 
time 2 = '18:59'
time 3 = '14:49'

i tryed this.

 SELECT
   convert(
    char(8),
    dateadd(
      second,
      SUM(
       DATEPART(
         hh,
         (convert(datetime,'12:03',1))
       )
       *
       3600
       +
       DATEPART(
         mi,
         (convert(datetime,'13:03',1))
       )
       *
       60
       +
       DATEPART(
         ss,
         (convert(datetime,'34:03',1))
       )
     ),
     0
   ),
   108
 )
 As Total

i got Error.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Solution

  • Just select the time which was you need to sum as hour min:

    CAST(sum(DATEDIFF(MINUTE, 0, time)) / 60 AS VARCHAR(8)) + ':' + FORMAT(sum(DATEDIFF(MINUTE, 0, time)) % 60, 'D2') AS [Totalhour]