Sum of time in sql

I have a table like this...

create table test
(开发者_JAVA技巧
  dt datetime
)

In that table the datetime are as follows,

2011/02/02 12:55:00
2011/03/05 00:40:00
2011/02/03 00:12:00

I want to calculate sum hours,mi,ss

In a single Select query not sp.

If any one know please tell me.

Thanks ...

---

You could sum the times as seconds, then convert to hours, minutes and seconds:

select TotalSeconds / 3600 as [Hours], (TotalSeconds % 3600) / 60 as [Minutes], (TotalSeconds % 3600) % 60 as [Seconds]
from
(
    select sum(datepart(hour, dt) * 3600) + sum(datepart(minute, dt) * 60) + sum(datepart(second, dt)) as TotalSeconds
    from test
) t

---

Assuming the sum won't be more than 999 hours:

DECLARE @t TABLE(dt DATETIME);

INSERT @t SELECT '20110202 12:55'
    UNION SELECT '20110305 00:40'
    UNION SELECT '20110203 00:12';

WITH s AS
(
    SELECT s = SUM(DATEDIFF(SECOND, 
        DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)), dt))
    FROM @t
)
SELECT 
    s,
    hhmmss = RIGHT('000' + RTRIM(s/3600), 3)
     + ':' + RIGHT('00' + RTRIM((s % 3600) / 60), 2)
     + ':' + RIGHT('00' + RTRIM((s % 3600) % 60), 2)
FROM s;

However, if what you are really storing is duration, why not store the number of seconds instead of wedging your data into an inappropriate data type that requires all kinds of workarounds to process properly?

---

Declare @Table Table 
(
    DateTimeCol DateTime
)
insert into @Table values ( '2011/02/02 12:55:00')
insert into @Table values ('2011/03/05 00:40:00')
insert into @Table values ('2011/02/03 00:12:00')

;with CTE As
(
    --first of all find total seconds of datecolumn
    --sum all seconds
    Select SUM(
        (datepart(hour,DateTimeCol)*60*60)+(datepart(minute,DateTimeCol)*60)+(datepart(second,DateTimeCol))
    ) As TotalSecond    
    From @Table
)
--devides with 3600 to get the total hours and then to 60 to get total minutes
Select CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' + 
CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' + 
CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [Time] --Total of Time
From CTE