How to get sum of time field in SQL server 2008

sql, sql-server, t-sql

Solution

if you have no minutes and only hours, then you can do something like:

select
    cast(sum(cast(replace(total_hours, ':', '') as int) / 100) as nvarchar(max)) + ':00'
from Table1
group by gs_cycle_no

if you don't, try this:

with cte as
(
    select
        gs_cycle_no,
        sum(cast(left(total_hours, len(total_hours) - 3) as int)) as h,
        sum(cast(right(total_hours, 2) as int)) as m
    from Table1
    group by gs_cycle_no
)
select
    gs_cycle_no,
    cast(h + m / 60 as nvarchar(max)) + ':' +
    right('00' + cast(m % 60 as nvarchar(max)), 2)
from cte

sql fiddle demo

Problem

I am facing a problem in finding the sum of values stored in a column, I have a table like this: ``` gs_cycle_no | from_time | to_time | total_hours(varchar) ... GSC-334/2012 | 13:00 | 7:00 | 42:00 GSC-334/2012 | 8:30 | 3:45 | 6:00 . . . ``` What i need to find is the `Sum(total_hours)` group by `gs_cycle_no`. But the `Sum` method will not work on the varchar column and also i cant convert it to decimal due to its format, How can i find the `sum` of `total_hours` column, based on `gs_cycle_no`?

Original source