sample --Sample data for this exercise Declare @Table Table
(
DateTimeCol DateTime
)
insert into @Table values ( '2015/03/02 12:55:00')
insert into @Table values ('2015/03/05 00:30:00')
insert into @Table values ('2015/03/03 00:22:00')
-- Query to calculate Total Time
;with CTE As
(
--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
Total Time
13.47.0