Sum of hour and minutes in SQL Server 2008

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

Dear sir,

i have two column i.e Hour and minute in datagrid. i want to disaply sum of hour and minutes.

EX- suppose i have entered in one column 2 Hour and 59 Minute and another column 2 Hour and 59 minutes. result should come Total 6.58(Means 6 hour and 58 minute). i need SQL queries.

Posted 25-Nov-13 2:38am

kamalsekhar

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-11-25T02:57:00

Try:

SQL

SELECT  CAST((SUM(HOURS)*60+SUM(MINS))/60 as float) + 
       (CAST((SUM(HOURS)*60+SUM(MINS))%60 as float)) / 100 
FROM MyTable
WHERE ...

I have tried again. i am not able to find out any difference in my code which one i posted. If you dont mind,please can you rectify my code and reply me.

Look at your code.
Your original question was about adding hours and minutes in two columns, and converting teh result to a float.

If what you are trying to do is add two hour columns to two minute columns, and return that as a float *for each row* then it is different: you need to convert the hours to minutes, add them together with the other two minutes, and convert the result back to hours and minutes, then convert that to a float.
So, you don't need SUM at all:

SQL

SELECT  CAST((Effort1*60 + Effort2 * 60 + Effort1Minutes + Effort2Minutes) / 60 as float) + 
       (CAST((Effort1*60 + Effort2 * 60 + Effort1Minutes + Effort2Minutes) % 60 as float)) / 100 
FROM MyTable
WHERE ...

See what I mean?

If you don't tell us what you want to start with, then you have to think about the answer we give rather than grab bits of it and hope! :laugh:

thatraja · Answer 2 · 2013-11-25T02:58:00

Solution 2

2 Hour 59 minutes + 2 Hour 59 minutes = not 6 Hour 58 minutes
the answer is 5 hour 58 minutes

Similar question has been answered 2 days ago, check it out
How to add minute into hours when minute is exceeding 60 minutes[^]

In SQL, you have aggregate functions like SUM, etc., using that you could calculate.

And you didn't show how are you saving the data in columns. If you stored like 2.59, then you have to split the values( 2 , 59) & convert the hours to minutes & add it to remaining minutes then sum two columns minutes values. Finally generate Hours - minutes format values from final minutes total.

Posted 25-Nov-13 2:58am

thatraja

Comments

kamalsekhar 25-Nov-13 12:47pm

I have written one queries.but it not giving correct answer.i want to display like this 5 Hour 58 minutes + 7 Hour 58 minutes=13 hour 56 minutes(answer should). but below queries giving 15.09 .please reply me with any other solution.(here Effort1 is 5 Hour column,Effort1Minutes is 58 minute column and same Effort2 is 7 Hour column,Effort2Minutes is 58 minute column

select
CAST((SUM(Effort1)*60+SUM(Effort1Minutes))/60 as float) +
(CAST((SUM(Effort1)*60+SUM(Effort1Minutes))% 60 as float)) / 100 +

CAST((SUM(Effort2)*60+SUM(Effort2Minutes))/60 as float) +
(CAST((SUM(Effort2)*60+SUM(Effort2Minutes))% 60 as float)) / 100

from TimesheetDetails