HH:MM:SS格式的DATEDIFF
HH:MM:SS格式的DATEDIFF
提问于 2013-01-22 03:13:51
我需要根据小时、分钟、秒和平均长度来计算总长度，给定一些具有开始时间和结束时间的数据。
例如，结果必须类似于45:15:10，它表示45小时15分10秒，或者30:07表示30分07秒。
我们正在使用SQL Server 2008 R2，当时间大于24:59:59时，转换失败。你知道我该怎么做吗？
对于信息，表中的列是Id，StartDateTime，EndDateTime等。我需要做一个月度报告，其中包含该月的记录计数，这些记录的总长度和平均长度。我想知道是否有一种简单的方法来执行所有这些操作。
回答 7
Stack Overflow用户
回答已采纳
发布于 2013-01-22 03:25:15
您不应该转换为time -它的目的是在单个24小时时钟上存储一个时间点，而不是一个持续时间或间隔(即使它本身被限制为<24小时，这显然不是您的数据)。相反，您可以以所需的最小间隔(在本例中为秒)获取datediff，然后执行一些数学和字符串操作，以所需的输出格式显示它(将秒数返回给应用程序或报告工具并让其完成此工作可能也是更可取的做法)。
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;此查询将上述结果中第二行的增量列从0:22:00更改为22:00。
Stack Overflow用户
发布于 2018-01-18 14:46:19
我稍微修改了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)
)
)Stack Overflow用户
发布于 2015-04-30 11:07:26
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)
)
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14445600
复制相关文章
相似问题
腾讯云开发者
