SQL Server: datediff函数在使用毫秒时导致溢出

I have the following query :

我有以下查询:

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

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."

当我执行此操作时，会得到一个错误:“datediff函数导致溢出。分隔两个日期/时间实例的数据部分的数量太大了。尝试使用datediff和不那么精确的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)

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

问题是我真的需要毫秒。

4 个解决方案

#1

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. see http://msdn.microsoft.com/en-us/library/ms189794.aspx

在毫秒内，startdate和enddate的最大区别是24天、20小时、31分钟和23.647秒。参见http://msdn.microsoft.com/en-us/library/ms189794.aspx

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

如果您需要千分之一秒以上的时间，您需要编写一些自定义的内容。

#2

A bit later response but may help. In SQL 2016 MS introduced function DATEDIFF_BIG which will (according to type size) overflow in difference bigger than something like 290k years. But technet article have same time difference as basic DATEDIFF - https://msdn.microsoft.com/en-us/library/mt628058.aspx

稍晚一点回应，但可能会有所帮助。在SQL 2016中，MS引入了函数DATEDIFF_BIG，它将(根据类型大小)溢出的差异大于290k年。但technet文章的时差与基本的DATEDIFF相同——https://msdn.microsoft.com/en-us/library/mt628058.aspx

#3

You don't need to refer to the miliseconds in your calculation.

您不需要在计算中引用毫秒数。

This will do exactly the same as your script except the overflow:

这将与您的脚本完全相同，除了溢出:

SELECT CONVERT(varchar(12), 
        CAST('2014-11-04 08:21:17.723' as datetime) - 
        CAST('2014-08-04 10:37:28.713' as datetime)
       , 114)

#4

For me there was a big interval between two dates so i have used below code

对于我来说，两个日期之间有一个很大的间隔，所以我使用了下面的代码

declare @timetagInMillsecond bigint=CAST(CAST( cast(@timetag as datetime) -'1970-01-01' AS decimal(38,10))*24*60*60*1000+0.5 as bigint)

声明@timetagInMillsecond bigint=CAST(CAST(CAST(@timetag为datetime)) -'1970-01-01'为decimal(38,10))*24*60*60*1000+0.5为bigint)

It works for me .

这对我很有效。

#1