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)

Share

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
Web04 | 2.8.140827.1 | Last Updated 11 Feb 2012
Article Copyright 2011 by Jobless Creature
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid