请教：如何获取每个时间累加全部时间。


想获得全部时间行累加的时间，结果如：72:05:22


数据库是：MSSQL 2005
[最优解释]
create table tb(dt varchar(10))
insert into tb select '20:05:27'
insert into tb select '12:40:34'
insert into tb select '03:55:29'
insert into tb select '11:12:16'
insert into tb select '17:25:44'
insert into tb select '23:48:39'
go
select ltrim(H+(m+s/60)/60)+':'+right('00'+ltrim((m+s/60)%60),2)+':'+right('00'+LTRIM(s%60),2) from (
select sum(datepart(hh,dt))h,sum(datepart(mi,dt))m,sum(DATEPART(s,dt))s from tb
)t
/*
----------------------
89:08:09

(1 行受影响)


*/
go
drop table tb
[其他解释]
楼上不对，如果超过24小时又会变为0

正确的如下：

with timetable AS
 (
select Convert(VARCHAR(30), GETDATE(),  108) AS  dt union all
select Convert(VARCHAR(30), GETDATE(),  108)   union all
select Convert(VARCHAR(30), GETDATE(),  108)   union all
select Convert(VARCHAR(30), GETDATE(),  108)  union all
select Convert(VARCHAR(30), GETDATE(),  108)   union all
select Convert(VARCHAR(30), GETDATE(),  108)  
) 

SELECT CONVERT(VARCHAR(10),sum(DATEDIFF(SECOND,'00:00:00',dt))/3600)+':'+CONVERT(VARCHAR(10),sum(DATEDIFF(SECOND,'00:00:00',dt))%3600/60)+':'+CONVERT(VARCHAR(10),sum(DATEDIFF(SECOND,'00:00:00',dt))%3600%60)+''
FROM timetable

[其他解释]

引用:

declare @t table(Ftime char(8))
insert into @t select '08:12:33'
insert into @t select '10:50:16'
insert into @t select '00:00:30'
insert into @t select '01:00:22'
insert into @t select '00:09:15……

declare @t table(Ftime char(8))
insert into @t select '08:12:33'
insert into @t select '10:50:16'
insert into @t select '40:00:30'
insert into @t select '01:00:22'
insert into @t select '00:09:15'
insert into @t select '00:10:18'
insert into @t select '31:00:50'
insert into @t select '50:00:40'
insert into @t select '00:00:30'
--测试
select convert(char(8),dateadd(ss,sum(datediff(ss,0,cast(Ftime as datetime))),0),108) As TimeSum
from @t
/*
消息 242，级别 16，状态 3，第 12 行
从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。
*/
[其他解释]
create table tb(dt datetime)
insert into tb select '00:05:07'
insert into tb select '02:20:34'
insert into tb select '03:55:29'
insert into tb select '01:12:16'
insert into tb select '07:15:44'
insert into tb select '03:38:39'
go
select ltrim(H+(m+s/60)/60)+':'+right('00'+ltrim((m+s/60)%60),2)+':'+right('00'+LTRIM(s%60),2) from (

select sum(datepart(hh,dt))h,sum(datepart(mi,dt))m,sum(DATEPART(s,dt))s from tb
)t
/*
----------------------
18:27:49

(1 行受影响)

*/
go
drop table tb
[其他解释]
declare @t table(Ftime char(8))
insert into @t select '08:12:33'
insert into @t select '10:50:16'
insert into @t select '00:00:30'
insert into @t select '01:00:22'
insert into @t select '00:09:15'
insert into @t select '00:10:18'
insert into @t select '01:00:50'
insert into @t select '00:00:40'
insert into @t select '00:00:30'
--测试
select convert(char(8),dateadd(ss,sum(datediff(ss,0,cast(Ftime as datetime))),0),108) As TimeSum
from @t
[其他解释]

create table #tb(dt datetime)
insert into #tb select '00:05:07'
insert into #tb select '02:20:34'
insert into #tb select '03:55:29'
insert into #tb select '01:12:16'
insert into #tb select '07:15:44'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '10:00:00'
go
declare @t datetime
select @t = cast(sum(CAST(dt AS float)) as datetime) from #tb
select cast(datediff(hh,'1900-01-01',@T)as varchar(10))+':'+cast(datepart(mi,@T)as varchar(10))+':'+cast(datepart(ss,@T)as varchar(10))
/*--------------------------------
43:2:25

(1 行受影响)
*/

[其他解释]
时间好像不能简单的相加，建议换算成秒再相加，然后再合计求值。
[其他解释]
OH~
sorry!
[其他解释]
就楼主测试的9行数据累加,怎么会有72:05:22呢?

[其他解释]
谢谢各位，使用6楼的方法解决问题了！