转换一下再sum:
select sum(cast(substring(PhoneTime,1,2) as int)*3600
+cast(substring(PhoneTime,4,2) as int)*60
+cast(substring(PhoneTime,7,2) as int)) 总时间秒
from t
select sum(cast(substring(PhoneTime,1,2) as int)*3600
+cast(substring(PhoneTime,4,2) as int)*60
+cast(substring(PhoneTime,7,2) as int)) 总时间秒
from t
declare @ table(a char(10))
insert into @ select '00:01:20' union all select '00:04:48'
select datepart(hour,asd) as 小时,datepart(minute,asd) as 分,datepart(second,asd) as 秒
from(
select dateadd(second,seconds,dateadd(minute,minutes,dateadd(hour,hours,'00:00:00' ))) as asd
from
(
select
sum (cast((case substring(a,1,2) when '00' then '0' else substring(a,1,2) end) as int)) as hours,
sum (cast((case substring(a,4,2) when '00' then '0' else substring(a,4,2) end) as int)) as minutes,
sum (cast((case substring(a,7,2) when '00' then '0' else substring(a,7,2) end) as int)) as seconds
from @
)a
)a--结果小时 分 秒
----------- ----------- -----------
0 6 8(1 row(s) affected)
declare @h int,@m int,@s int
select @h=0,@m=0,@s=0
select @h=sum(cast(substring(PhoneTime,1,charindex(':',PhoneTime)) as int)),
@m=sum(cast(substring(right(PhoneTime,5),1,charindex(':',PhoneTime)) as int)),
@s=sum(cast(right(PhoneTime,2) as int))
from t
print cast(@h+(@m*60+@s)/3600 as varchar)+':'+cast((@m+@s/60)%60 as varchar)+':'+cast(@s%60 as varchar)