计算具有 Time DataType 的列的 SUM: [英] Calculate the SUM of the Column which has Time DataType:

问题描述

我想计算具有时间数据类型的字段的总和.

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

但我想要的输出如下:

            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

在 SQL Fiddle 工作代码.

这篇关于计算具有 Time DataType 的列的 SUM:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！