本文介绍了计算具有Time数据类型的列的SUM:的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想计算具有时间数据类型的字段的总和.
I want to calculate the Sum of the Field which has Time DataType.
我的桌子在下面:
TableA:
TotalTime
-------------
12:18:00
12:18:00
在这里,我想对两个时间字段求和.我尝试了以下查询
Here I want to sum the two time fields.I tried the below Query
SELECT CAST(
DATEADD(MS, SUM(DATEDIFF(MS, '00:00:00.000',
CONVERT(TIME, TotalTime))), '00:00:00.000'
) AS TOTALTIME)
FROM [TableA]
但是它给出的输出为
TOTALTIME
-----------------
00:36:00.0000000
但是我想要的输出如下:
But My Desired Output would be like below:
TOTALTIME
-----------------
24:36:00
如何获取此输出?
推荐答案
您可以求和总计秒数或datediff(second,0,datecolumn)
.您可以使用一些数学将其格式化为时间字符串.例如,分钟总数为totalseconds / 60 % 60
.例如:
You could sum the total number of seconds, or datediff(second,0,datecolumn)
. You can format that as a time string with some math. For example, the total number of minutes is totalseconds / 60 % 60
. For example:
select cast(sum(datediff(second,0,dt))/3600 as varchar(12)) + ':' +
right('0' + cast(sum(datediff(second,0,dt))/60%60 as varchar(2)),2) +
':' + right('0' + cast(sum(datediff(second,0,dt))%60 as varchar(2)),2)
from TestTable
这篇关于计算具有Time数据类型的列的SUM:的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!