Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Adding Time in HH:MM Format - Useful SQL Query

4.50/5 (2 votes)
11 Feb 2012CPOL 48.6K  
HH:MM Time format addition
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-----------------------

SQL
CREATE TABLE mytable
(
timeduration varchar(25)
)

INSERT INTO mytable VALUES ('05:30')
INSERT INTO mytable values ('05:33')
INSERT INTO mytable values ('04:33')

-- OUTPUT SHOULD BE 15:36 MINUTES
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---------------------------------

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)