In one of our projects related to employee shift management, the shift details are captured and total hours worked per day is stored in HH:MM format. A new reporting requirement is that we need to display the total hours worked per week in HH:MM format. Rather than retrieving the data in HH:MM format, splitting and adding, we wrote a query which adds and retrieves the total hours in HH:MM format.
A simplified version in the script format is given below; It adds the time duration stored in HH:MM format;
-------------Start-----------------------
CREATE TABLE mytable
(
timeduration varchar(25)
)
INSERT INTO mytable VALUES ('05:30')
INSERT INTO mytable values ('05:33')
INSERT INTO mytable values ('04:33')
select * from mytable
select CAST
(
(SUM (datepart(hh, convert (varchar, timeduration, 108))) +
(sum(datepart(mi, convert (varchar, timeduration, 108)))/60) ) AS VARCHAR(2)
)
+ ':' +
CAST
(
sum(datepart(mi, convert (varchar, timeduration, 108))) - 60 * (sum(datepart(mi, convert (varchar, timeduration, 108)))/60)
as VARCHAR(2))
from mytable
--------------------End---------------------------------