SQL DateDiff在数分钟内整夜运行
SQL DateDiff在数分钟内整夜运行
提问于 2018-12-07 03:58:44
尝试获取日期时间字段和时间字段之间的分钟差值。我使用的是datediff函数。
当开始时间从一个日期开始，比如'2018-01-08 22:35:55.043‘，结束时间是第二天，比如'00:35:56.2136644'，结果是从结束时间到开始时间。
示例：
select DATEDIFF(MINUTE, CAST('2018-01-08 22:35:55.043' AS TIME), '00:35:56.2136644') AS minDiff1
select DATEDIFF(MINUTE, '00:35:56.2136644', CAST('2018-01-08 22:35:55.043' AS TIME)) AS minDiff2
select DATEDIFF(MINUTE, CONVERT(TIME, '2018-01-08 22:35:55.043'), '00:35:56.2136644') AS minDiff3
select DATEDIFF(MINUTE, '00:35:56.2136644', CONVERT(TIME, '2018-01-08 22:35:55.043')) AS minDiff4结果和我预期的不一样。期望的结果将是120分钟。
minDiff1 = -1320
minDiff2 = 1320
minDiff3 = -1320
minDiff4 = 1320原始查询
select DATEDIFF(MINUTE, CAST(test_start_datetime as TIME), test_end_time) AS minDiff
from user_exam 回答 2
Stack Overflow用户
回答已采纳
发布于 2018-12-07 04:15:40
以下假设时间属于同一天或下一天：
SELECT *, CASE
-- same day -- start time is less than end time
WHEN CAST(datetimecol AS time) <= timecol THEN DATEDIFF(MINUTE, CAST(datetimecol AS time), timecol)
-- next day -- start time is more than end time (it rolled over into next day)
ELSE 1440 - DATEDIFF(MINUTE, timecol, CAST(datetimecol AS time))
END
FROM (VALUES
(CAST('2018-01-08 22:35:55.043' AS DATETIME), CAST('22:35:55.0433333' AS TIME)),
(CAST('2018-01-08 22:35:55.043' AS DATETIME), CAST('23:35:56.2136644' AS TIME)),
(CAST('2018-01-08 22:35:55.043' AS DATETIME), CAST('00:35:56.2136644' AS TIME))
) AS tests(datetimecol, timecol)在上面的示例中，1440是24小时内的分钟数。
Stack Overflow用户
发布于 2018-12-07 04:21:53
如果你想看到120，那么平均时间差对你来说是不够的。对于你的问题，你想找出22:35和第二天00:35之间的差异，你需要找出日期时间的差异，就像这样：
SELECT DATEDIFF(MINUTE, CAST(GETDATE() AS DATETIME)
+CAST(CAST('22:35:55.2136644' AS TIME) AS DATETIME)
,
CAST(GETDATE() AS DATETIME)+1
+CAST(CAST('00:35:56.2136644' AS TIME)AS DATETIME)
) AS minDiff1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53658793
复制相关文章
相似问题
腾讯云开发者
