SQL Server 2008中的小时和分钟总和 [英] Sum of hour and minutes in SQL Server 2008

问题描述

亲爱的先生，



我有两列，即数据网格中的小时和分钟。我想要小时和分钟的总和。



EX-假设我输入了一列2小时59分钟和另一列2小时59分钟。结果应该是总计6.58（表示6小时58分钟）。我需要SQL查询。

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.

推荐答案

尝试：

Try:

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

我再试一次。我无法找到我发布的代码中的任何差异。如果你不介意，请你纠正我的代码并回复我。





看看你的代码。 />
您最初的问题是在两列中添加小时和分钟，并将结果转换为浮点数。



如果你想要做什么将两小时列添加到两分钟列，并将其作为每行的*返回*然后它是不同的：您需要将小时数转换为分钟，将其与其他两分钟一起添加，并将结果转换回小时和分钟，然后将其转换为浮点数。

因此，您根本不需要SUM：

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:

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:

2小时59分钟+2小时59分钟= 不 6小时58分钟

答案是5小时58分钟



类似的问题已经回答2天前，检查出来

如何在分钟超过60分钟时添加分钟数 [ ^ ]



在SQL中，您有聚合函数，如 SUM 等，使用你可以计算。



你没有说明如何将数据保存在列中。如果你存储像2.59，那么你必须拆分值（2,59）&将小时数转换为分钟数&将其添加到剩余的分钟，然后将两列分钟值相加。最后生成小时 - 最终分钟总数的分钟格式值。

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.

这篇关于SQL Server 2008中的小时和分钟总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！