Time Addition Problem

[AliAmjad]

I am using the following code to Add up the difference of two time fields but It's not showing the correct record when the hours part increases above 24 means It shows the correct addition only if It's between 24 hours. For example WaitingTimeTotal (hh:mm:ss) ---------------- 03:18:46 03:25:06 ________ 06:43:52 which is correct but WaitingTimeTotal (hh:mm:ss) ---------------- 03:18:46 03:25:06 16:23:46 07:24:20 ________ wrong answer!!! The code i am using is

convert(varchar,convert(DateTime, SUM(convert(real, ConfTime - EnterTime))),108)WaitingTimeTotal

Can you please guide me. Thanks in advance !

AliAmjad(MCP) First make it Run THEN make it Run Fast!

modified on Thursday, January 15, 2009 1:28 AM

[Wendelius]

If you want to have total hour amount, this could be one way. I used inline views to both generate a test data (inner one) and to ease the summary (the outer one).

select convert(varchar(50), datepart(hour, datesum) + (datepart(day, datesum) - 1) * 24)
+ ':' + convert(varchar(50), datepart(minute, datesum))
+ ':' + convert(varchar(50), datepart(second, datesum))
from (select SUM(convert(real, datecolumn)) as datesum
from (select CONVERT(datetime, '03:18:46') as datecolumn
union
select CONVERT(datetime, '03:25:06') as datecolumn
union
select CONVERT(datetime, '16:23:46') as datecolumn
union
select CONVERT(datetime, '07:24:20') as datecolumn) alias1
) alias2

Now the result is 30:31:57. Was this what you were after?

The need to optimize rises from a bad design.My articles[^]