time合计怎么写

Question

数据库A如下,我现在要对time列进行合计，select sum(time) as sumtime from A ,结果：31:10:30m,应该怎么写语句？

time  
08:30:00
09:30:00
12:00:00
01:10:30

Answer 1

Declare @hour int,@minute int,@second int SELECT @hour=SUM(DATEPART(HOUR,[time])),@minute=SUM(DATEPART(MINUTE,[time])),@second=SUM(DATEPART(SECOND,[time])) FROM #temp Set @minute+=@second/60 Set @second=@second%60 Set @hour+=@minute/60 SET @minute=@minute%60 Print REPLACE(STR(@hour) + ':' + STR(@minute)+':'+STR(@second),' ','')  

Answer 2

写个存储过程吧。