How to sum multiple hour : mins in sql?

Question

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.

Could you please first explain the desired result.If we add 13:56 to 14:49 what should we get? — Sergey, Apr 21 '22 at 13:54
Which dbms are you using? (Several product specific functions are used above.) — jarlh, Apr 21 '22 at 13:58
Exactly what `DATETIME` do you expect this to give? `convert(datetime,'34:03',1)`, there is no such time... — MatBailie, Apr 21 '22 at 14:01
Looks like you should parse the input stings, compute total minutes using int arithmetic (or intervals, depending on DBMS) and format a result as a string you want in your code as the input doesn't represent values allowed by datetime time or timestamp data types. — Serg, Apr 21 '22 at 14:20
I would like to sum the three value as a time. time 1,time 2 and time3 — Dinesh Thangadurai, Apr 22 '22 at 02:53

score 0 · Accepted Answer · edited May 17 '22 at 06:53

0

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]

edited May 17 '22 at 06:53

Andronicus

25,419
17
47
88

answered May 16 '22 at 11:45

Dinesh Thangadurai

11
2

How to sum multiple hour : mins in sql?

1 Answers1