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
楼上不对,如果超过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 '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 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
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 行受影响) */
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
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
(
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
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 数据类型的转换产生一个超出范围的值。
*/
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
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 行受影响)
*/