Calculating total time in HH:MI:SS

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 433
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

Comments or Responses

Login to post response