Click here to Skip to main content
15,898,134 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.5K   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 
Reason for my vote of 5
Excellent manipulation of time string, here at ESC we still have systems over 20 systems running SQL 2K0. Thanks for the passing on the knowledge.

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.