SQL Server:datediff 函数在使用 MILLISECOND 时导致溢出 [英] SQL Server: datediff function resulted in an overflow when using MILLISECOND

问题描述

我有以下查询:

select CONVERT(varchar(12), DATEADD(MILLISECOND, DateDiff(MILLISECOND, '2014-08-04 10:37:28.713','2014-11-04 08:21:17.723'), 0), 114)

当我执行此操作时，出现错误:datediff 函数导致溢出.分隔两个日期/时间实例的日期部分的数量太大.尝试使用具有不太精确的日期部分的 datediff."

When I execute this, I get the error : "The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart."

当我将查询更改为以下内容时，它可以正常工作:

When I change the query to the following it works fine :

select CONVERT(varchar(12), DATEADD(SECOND, DateDiff(SECOND, '2014-08-04 10:37:28.713','2014-11-04 08:21:17.723'), 0), 114)

问题是我真的也需要 MILLISECONDS.

The problem is that I really need the MILLISECONDS as well.

推荐答案

参见 https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15#return-value

对于毫秒，startdate 和 enddate 之间的最大差异为 24 天 20 小时 31 分 23.647 秒.

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds.

如果您需要高于该级别的毫秒，则需要编写一些自定义内容.

If you need millisecond above that level, you'll need to write something custom.

这篇关于SQL Server:datediff 函数在使用 MILLISECOND 时导致溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！