time合计怎么写

Question

数据库A如下,我现在要对time列进行合计，select sum(time) as sumtime from A ,结果：31:10:30m,应该怎么写语句？time  08:30:0009:30:0012:00:0001: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

写个存储过程吧。