Friday, February 24, 2012

Adding Hours, Minutes, Seconds (SQL 2000)

Hi There,
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"
Any ideas?
Using SQL 2000try this tricky thing...

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)

now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)

sum1
----
47:10:15

Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5

JulianDayNo
-------
2454154.9927028548|||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
from @.TimeStrings|||UPalsen's way works - thanx

No comments:

Post a Comment