Click here to Skip to main content
12,253,908 members (66,087 online)
Click here to Skip to main content
Add your own
alternative version

Stats

32.3K views
4 bookmarked
Posted

Adding Time in HH:MM Format - Useful SQL Query

, 11 Feb 2012 CPOL
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) TATA Communications
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.

You may also be interested in...

Comments and Discussions

 
QuestionMM addition has one small problem Pin
srikrishnathanthri28-Jul-15 2:24
membersrikrishnathanthri28-Jul-15 2:24 
Questionsyntax error Pin
Member 1048265122-Dec-13 18:12
memberMember 1048265122-Dec-13 18:12 
AnswerRe: syntax error Pin
Jobless Creature22-Dec-13 19:28
memberJobless Creature22-Dec-13 19:28 
GeneralReason for my vote of 4 NICE Pin
Sushil Kumar Purohit18-Feb-12 0:55
memberSushil Kumar Purohit18-Feb-12 0:55 
GeneralReason for my vote of 5 Excellent manipulation of time strin... Pin
cbragdon13-Jun-11 13:41
membercbragdon13-Jun-11 13:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 11 Feb 2012
Article Copyright 2011 by Jobless Creature
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid