在 sql 中计算 HH:MM:SS 中的日期时间总和 [英] Calculate Sum of Datetime in HH:MM:SS in sql

问题描述

我想用 HH:MM:SS 计算总和

I want to calculate Sum in HH:MM:SS

首先我正在计算 HH:MM:SS 中的日期时间差异(效果很好)

First i am calculating datetime Difference in HH:MM:SS (works well)

Select  * 
Into    #_TicketType 
From    (    
        Select  *,
                convert(varchar(5), DateDiff(s, [Start Date] , [End Date] )/3600)
              + ':'
              + convert(varchar(5),DateDiff(s, [Start Date] , [End Date])%3600/60)
              + ':'
              + convert(varchar(5),(DateDiff(s,[Start Date] , [End Date])%60)) as [Ticket Type Time]
            ,
                convert(varchar(5),DateDiff(s, [Ticket Open Time]  , [Ticket Closed Time] )/3600)
              + ':'
              + convert(varchar(5),DateDiff(s,[Ticket Open Time]  , [Ticket Closed Time])%3600/60)
              + ':'
              + convert(varchar(5),(DateDiff(s,[Ticket Open Time]  , [Ticket Closed Time])%60))[Total Call Time]
        FROM CTE
        )_A

列 [Start Date] , [End Date], [Ticket Open Time] , [Ticket Closed Time] 类型 DateTime

另外我想在 HH:MM:ss 中计算它的总和

Further i want to calculate sum of it in HH:MM:ss

我的查询:-

Select Distinct ATM,
    CAST (
          (SUM (datepart(hh, convert (varchar, [Ticket Type Time], 108)))
        + (sum (datepart(mi, convert (varchar, [Ticket Type Time], 108)))/60) ) 
    AS VARCHAR(2))
    + ':'
    + CAST (
          sum( datepart(mi, convert (varchar, [Ticket Type Time], 108))) 
        - 60 
        * (sum(datepart(mi, convert (varchar, [Ticket Type Time], 108)))/60)
    as VARCHAR(2))
    + ':' 
    + CAST (
          sum( datepart(ss, convert (varchar, [Ticket Type Time], 108))) 
        - 60 
        * (sum( datepart(SS, convert (varchar, [Ticket Type Time], 108)))/60)
    as VARCHAR(2))
From #_TicketType Group By ATM

它给了我错误

**Msg 241, Level 16, State 1, Line 67
Conversion failed when converting date and/or time from character string.**

数据库 SQL SERVER 2008

推荐答案

我认为您打算转换为时间(或日期时间，如果您的 SQL Server 版本不支持它)，而不是 varchar.

I think you meant to convert to time (or datetime if your version of SQL Server doesn't support it), rather than varchar.

但是，这是一种非常糟糕的方法.25:01:01 将不是有效的日期时间.您应该总结秒数(如另一个答案中的建议)，然后在最后根据您的喜好格式化结果.

But, this is a very bad way to do this. 25:01:01 will not be a valid datetime. You should sum up the seconds (as suggested in another answer), and then format the results as you like at the end.

因此，在您的第一个查询中，包含另一列以秒为单位的持续时间:

So, in your first query, include another column that's the duration in seconds:

, DateDiff(s,[Start Date] , [End Date]) as [Ticket Type Seconds]

然后在第二个查询中，只需格式化 SUM([Ticket Type Seconds])，可能像这样:

Then in the second query, just format the SUM([Ticket Type Seconds]), perhaps like this:

STR(SUM([Ticket Type Seconds])/3600) 
+ RIGHT(CONVERT(char(8),DATEADD(s,SUM([Ticket Type Seconds]),0),108),6)

这为您提供了小时数(可以超过 24 小时)，并与转换秒数时获得的日期时间的 ':MM:SS' 部分连接(添加到 0 日期:19000101 00:00:00) 到日期时间.

That gives you the hours (that can be more than 24), concatenated with the ':MM:SS' part of the datetime that you get when you convert the seconds (added to the 0 date: 19000101 00:00:00) to datetime.

这篇关于在 sql 中计算 HH:MM:SS 中的日期时间总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！