在SQL中计算日期时间差的总和（以小时.分钟表示）

Question

我有一个SQL表，其中DateDiff的格式为（hh.mm）

DateDiff   ATM
1.45       121
1.50       121
1.50       121

当我在ATM上通过Group by执行Sum日期差时，结果会显示为：

4.45    121

但实际上日期时间差应该是：

5.25     121

如何在 SQL 查询中实现相同的功能。

Select Sum(Cast(Convert(Varchar(10),DATEDIFF(HOUR,TicketRaisedOn,ClosedOn))+'.'+
Convert(Varchar(10),DATEDIFF(MINUTE,TicketRaisedOn,ClosedOn)%60) as Numeric(18,2)))[Down Time],ATM 
From Ticket Where Closed=1 And DATEPART(DAYOFYEAR,GETDATE())=DATEPART(DAYOFYEAR,TicketRaisedOn) 
Group BY ATM Order By [Down Time] Desc

TicketRaisedOn 和 ClosedOn 是日期时间类型。

数据库是 SQL Server 2008。

上面的查询将会输出类似以下的结果（但是其错误，因为它将会把它们当作数字求和而不是日期时间格式）。

Down Time       ATM
16.95           282
14.46           1811
14.20           52
14.04           936

示例数据

Select TicketRaisedOn,ClosedOn,ATM 
From Ticket 
Where ATM=282 And DATEPART(DAYOFYEAR,GETDATE())=DATEPART(DAYOFYEAR,TicketRaisedOn) 
And Closed=1

TicketRaisedOn          ClosedOn                ATM
2012-12-21 01:15:23.793 2012-12-21 15:11:59.240 282
2012-12-21 16:42:29.820 2012-12-21 18:21:30.797 282

- Shaggy

---

我怀疑在求和运算之前，这个Numeric(18,2)类型是经过转换的。 - Aviram Segal

上面的数据是查询结果吗？如果是的话，能否提供您表中的数据？ - Taryn

一些关于字段 TicketRaisedOn,ClosedOn, Closed 的数据和表结构？ - bonCodigo

@bonCodigo TicketRaisedOn和ClosedOn是SQL中的DateTime数据类型。我只是检索当前日期的记录，而Closed是位（Bit）类型。 - Shaggy

第一行可能会误导，表中的数据以 DATETIME 形式存储，所需输出为 HH.MM。 - msmucker0527

Answer 1

在格式化之前进行求和

SELECT
  ATM,
  CONVERT(VARCHAR(10), SUM(DATEDIFF(Minute, TicketRaisedOn, ClosedOn)) / 60)
  + '.' +
  RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, TicketRaisedOn, ClosedOn)) % 60), 2)
FROM Ticket
GROUP BY ATM

Sql Fiddle: http://sqlfiddle.com/#!3/eca01/1

- msmucker0527

---

这可能会变成11:6而不是11:06 - 你需要在右侧填充左边。 - Aaron Bertrand

Answer 2

你所做的并不起作用，因为1.5代表一个半小时，而不是一个小时五十分钟。 要得到你想要的结果，请在较小的单位上进行算术运算，例如秒，然后将其转换为日期时间，最后再将日期时间转换为最终表示。以下是一个示例：

select right(convert(varchar(255),
                     DATEADD(second, DATEDIFF(s, TicketRaisedOn,ClosedOn), cast(0 as datetime)),
                     120),
             8)

- Gordon Linoff