Click here to Skip to main content
15,887,966 members
Articles / Database Development / SQL Server
Tip/Trick

Adding Time in HH:MM Format - Useful SQL Query

Rate me:
Please Sign up or sign in to vote.
4.50/5 (2 votes)
11 Feb 2012CPOL 47.3K   4   5
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)


Written By
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.

Comments and Discussions

 
QuestionMM addition has one small problem Pin
srikrishnathanthri28-Jul-15 2:24
srikrishnathanthri28-Jul-15 2:24 
Questionsyntax error Pin
Member 1048265122-Dec-13 18:12
Member 1048265122-Dec-13 18:12 
AnswerRe: syntax error Pin
Jobless Creature22-Dec-13 19:28
professionalJobless Creature22-Dec-13 19:28 
GeneralReason for my vote of 4 NICE Pin
Sushil Kumar Purohit18-Feb-12 0:55
Sushil Kumar Purohit18-Feb-12 0:55 
GeneralReason for my vote of 5 Excellent manipulation of time strin... Pin
cbragdon13-Jun-11 13:41
cbragdon13-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.