var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Adding Hours, Minutes, Seconds (SQL 2000)
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Using SQL 2000
try this tricky thing....
now dont ask me what will happen if the sum is more than 24 hrs etc. etc...
declare @Dt as datetime
set @Dt = '2007-02-20'
declare @Dt1 as datetime
set @Dt1 = '2007-02-20 01:50:10'
declare @Dt2 as datetime
set @Dt2 = '2007-02-20 01:20:05'
select convert(varchar,cast((cast(@Dt1 as float) - cast(@Dt as float)) + (cast(@Dt2 as float) - cast(@Dt as float)) as datetime),114)
You'll need to verify that the above function syntax is correct, but you should get the general idea.
select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
declare @tm1 datetime, @tm2 datetime
select @tm1='23:50:10', @tm2='23:20:05'
Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
Last edited by pdreyer; 02-26-07 at
I really don't think the formula needs to be that complicated...
set nocount on
declare @TimeStrings table (TimeString varchar(8))
insert into @TimeStrings (TimeString) values ('01:50:10')
insert into @TimeStrings (TimeString) values ('01:20:5')
select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
UPalsen's way works - thanx