Click here to Skip to main content
Click here to Skip to main content

Adding Time in HH:MM Format - Useful SQL Query

, 11 Feb 2012
Rate this:
Please Sign up or sign in to vote.
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-----------------------
 
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)

About the Author

Jobless Creature
Software Developer (Senior)
India India
I have a total experience of around 5 years out of which 4 years in MS technologies. I have a passion towards coding.

Comments and Discussions

 
Questionsyntax error PinmemberMember 1048265122-Dec-13 18:12 
AnswerRe: syntax error PinmemberJobless Creature22-Dec-13 19:28 
GeneralReason for my vote of 4 NICE PinmemberSushil Kumar Purohit18-Feb-12 0:55 
GeneralReason for my vote of 5 Excellent manipulation of time strin... Pinmembercbragdon13-Jun-11 13:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 11 Feb 2012
Article Copyright 2011 by Jobless Creature
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid