我需要根据一些具有开始时间和结束时间的数据，计算总时长，包括小时、分钟和秒数，并计算平均值。
例如，结果应该是类似于45:15:10表示45小时15分钟10秒，或者30:07表示30分钟7秒。
我们使用的是SQL Server 2008 R2，当时间超过24:59:59时，转换会失败。您有什么想法可以解决这个问题吗？
表中的列为Id、StartDateTime、EndDateTime等。我需要制作一个每月报告，其中包含该月的记录数量、这些记录的总长度和平均长度。请问是否有简便的方法来完成所有这些操作呢？
你不应该将时间转换为 time - 它的意义在于存储单个24小时时钟上的某一点，而不是一个持续时间或时间间隔（即使它的时间限制在 < 24 小时内，显然你的数据也不是这种情况）。相反，你可以用最小需要的间隔（在你的例子中是秒）来计算日期差值，然后进行一些数学和字符串操作以呈现你所需的输出格式（将秒返回给应用程序或报告工具并让其完成此工作可能也是可取的）。
DECLARE @d TABLE
(
id INT IDENTITY(1,1),
StartDateTime DATETIME,
EndDateTime DATETIME
);
INSERT @d(StartDateTime, EndDateTime) VALUES
(DATEADD(DAY, -2, GETDATE()), DATEADD(MINUTE, 15, GETDATE())),
(GETDATE() , DATEADD(MINUTE, 22, GETDATE())),
(DATEADD(DAY, -1, GETDATE()), DATEADD(MINUTE, 5, GETDATE())),
(DATEADD(DAY, -4, GETDATE()), DATEADD(SECOND, 14, GETDATE()));
;WITH x AS (SELECT id, StartDateTime, EndDateTime,
d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
FROM @d
)
SELECT id, StartDateTime, EndDateTime,
[delta_HH:MM:SS] = CONVERT(VARCHAR(5), d/60/60)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
[avg_HH:MM:SS] = CONVERT(VARCHAR(5), a/60/60)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;
结果：
id StartDateTime EndDateTime delta_HH:MM:SS avg_HH:MM:SS
-- ------------------- ------------------- -------------- ------------
1 2013-01-19 14:24:46 2013-01-21 14:39:46 48:15:00 42:10:33
2 2013-01-21 14:24:46 2013-01-21 14:46:46 0:22:00 42:10:33
3 2013-01-20 14:24:46 2013-01-21 14:29:46 24:05:00 42:10:33
4 2013-01-17 14:24:46 2013-01-21 14:25:00 96:00:14 42:10:33
这并不完全符合您的要求，因为它不会仅显示小时数小于1小时的MM:SS。 您可以通过简单的CASE表达式进行调整：
;WITH x AS (SELECT id, StartDateTime, EndDateTime,
d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
FROM @d
)
SELECT id, StartDateTime, EndDateTime,
[delta_HH:MM:SS] = CASE WHEN d >= 3600 THEN
CONVERT(VARCHAR(5), d/60/60) + ':' ELSE '' END
+ RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
[avg_HH:MM:SS] = CASE WHEN a >= 3600 THEN
CONVERT(VARCHAR(5), a/60/60) + ':' ELSE '' END
+ RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;
此查询将上面结果中第二行的 delta 列从 0:22:00 更改为 22:00。
我稍微修改了Avinash的答案，因为如果差异太大，它可能会以错误结束。 如果您只需要HH:mm:ss，那么只需要在秒级别上进行区分，如下所示：
SELECT CONVERT(time,
DATEADD(s,
DATEDIFF(s,
'2018-01-07 09:53:00',
'2018-01-07 11:53:01'),
CAST('1900-01-01 00:00:00.0000000' as datetime2)
)
)
SELECT CONVERT(time,
DATEADD(mcs,
DATEDIFF(mcs,
'2007-05-07 09:53:00.0273335',
'2007-05-07 09:53:01.0376635'),
CAST('1900-01-01 00:00:00.0000000' as datetime2)
)
)
如果您想进行平均数计算，最好的方法是将时间转换为秒或者一天的分数。在SQL Server中，一天的分数很方便，因为您可以执行以下操作：
select avg(cast(endtime - starttime) as float)
from t
datetime类型：select cast(avg(cast(endtime - starttime as float) as datetime)
from t
获取所需格式的时间算术运算...这很麻烦。您可以考虑在最终格式中包含天数，并使用以下方式：
select right(convert(varchar(255), <val>, 120), 10)
select cast(floor(cast(<val> as float)*24) as varchar(255))+right(convert(varchar(255), <val>, 120), 6)
convert 来处理分钟和秒，需要在左侧填充 0。然后将小时作为单独的值附加在后面。从SQL SERVER 2012开始，您无需使用DATEDIFF函数。您可以使用FORMAT函数来实现您想要的效果：
SELECT
FORMAT(CONVERT(TIME, [appoitment].[Start] - [appointment].[End]), N'hh\:mm') AS 'Duration'
FROM
[tblAppointment] (NOLOCK)
DECLARE @startDate AS DATETIME = '2018-06-01 14:20:02.100'
DECLARE @endDate AS DATETIME = '2018-06-02 15:23:09.000'
SELECT CAST(DATEDIFF(day,'1900-01-01', @endDate - @startDate) AS VARCHAR) + 'd ' + CONVERT(varchar(22), @endDate - @startDate, 114)
以上将返回
1d 01:03:06:900
当然，您可以使用您选择的格式
SQL支持日期时间减法，它会输出相对于最小日期（例如1900-01-01，您可能可以从某些系统变量中获取此值）的新日期时间。这比DATEDIFF更好，因为DATEDIFF将计算“跨越日期部分边界”的次数，即使经过的时间不到一个完整的日期部分。此方法的另一个好处是它允许您使用日期格式转换。
days是天数（正数），例如0.5代表12小时，则可以使用以下表达式将其格式化为正确的持续时间：CONVERT(varchar(9), FLOOR(days * 24)) + RIGHT(CONVERT(char(19), CAST(days AS datetime), 120), 6)
当粘贴时，Excel将理解高达9999:59:59的值。在英文版本中应用自定义格式：[h]:mm:ss（荷兰语为[u]:mm:ss）。