为什么GETDATE()和SYSDATETIME()之间的Datediff总是不同的? [英] Why Datediff between GETDATE() and SYSDATETIME() in milliseconds is always different?

问题描述

我试图在GETDATE()和SYSDATETIME()之间获得Datediff，以毫秒为单位.

I am trying to get Datediff between GETDATE() and SYSDATETIME() in milliseconds.

SELECT DATEDIFF(ms, GETDATE() , SYSDATETIME());        

我得到的结果是0或1或2或3.造成这种差异的原因是什么?

The result I am getting is 0 or 1 or 2 or 3. What is the reason for this difference?

查看此小提琴.

推荐答案

它们是两个不同的函数调用，可以返回两个不同的时间.

They are two different function calls that can return two different times.

此外，GETDATE返回的datetime数据类型仅具有3-4毫秒的精度，而SYSDATETIME()返回的是datetime2(7)数据类型.

Additionally GETDATE returns a datetime datatype which only has precision of 3-4 ms whereas SYSDATETIME() returns a datetime2(7) datatype.

即使两个调用都在完全相同的时间返回，您也会看到由于四舍五入而遇到的问题.

Even if both calls were to return exactly the same time you could see the issue that you are experiencing due to rounding.

DECLARE @D1 DATETIME2 = '2012-08-18 10:08:40.0650000'
DECLARE @D2 DATETIME = @D1 /*Rounded to 2012-08-18 10:08:40.067*/
SELECT DATEDIFF(ms, @D1 , @D2) /*Returns 2*/

另一个答案是不正确的，如果用GETDATE()代替，则该函数仅被调用一次，如下所示.

The other answer is incorrect that if you substitute in GETDATE() the function is only called once as can be demonstrated from the below.

WHILE DATEDIFF(ms, GETDATE() , GETDATE()) = 0 
PRINT 'This will not run in an infinite loop'

使用GETDATE()和SYSDATETIME在Windows XP桌面上运行循环时，我还可以看到指示可能也发生其他情况的结果.也许调用其他API.

When running a loop on my windows XP desktop with GETDATE() and SYSDATETIME I can also see results that indicate that something else might be going on as well though. Perhaps calling a different API.

CREATE TABLE #DT2
  (
     [D1] [DATETIME2](7),
     [D2] [DATETIME2](7)
  )

GO

INSERT INTO #DT2
VALUES(Getdate(), Sysdatetime())

GO 100

SELECT DISTINCT [D1],
                [D2],
                Datediff(MS, [D1], [D2]) AS MS
FROM   #DT2

DROP TABLE #DT2 

下面的示例结果

+-----------------------------+-----------------------------+-----+
|             D1              |             D2              | MS  |
+-----------------------------+-----------------------------+-----+
| 2012-08-18 10:16:03.2500000 | 2012-08-18 10:16:03.2501680 |   0 |
| 2012-08-18 10:16:03.2530000 | 2012-08-18 10:16:03.2501680 |  -3 |
| 2012-08-18 10:16:03.2570000 | 2012-08-18 10:16:03.2501680 |  -7 |
| 2012-08-18 10:16:03.2600000 | 2012-08-18 10:16:03.2501680 | -10 |
| 2012-08-18 10:16:03.2630000 | 2012-08-18 10:16:03.2501680 | -13 |
| 2012-08-18 10:16:03.2630000 | 2012-08-18 10:16:03.2657914 |   2 |
| 2012-08-18 10:16:03.2670000 | 2012-08-18 10:16:03.2657914 |  -2 |
| 2012-08-18 10:16:03.2700000 | 2012-08-18 10:16:03.2657914 |  -5 |
| 2012-08-18 10:16:03.2730000 | 2012-08-18 10:16:03.2657914 |  -8 |
| 2012-08-18 10:16:03.2770000 | 2012-08-18 10:16:03.2657914 | -12 |
| 2012-08-18 10:16:03.2800000 | 2012-08-18 10:16:03.2814148 |   1 |
+-----------------------------+-----------------------------+-----+

感兴趣的行是

| 2012-08-18 10:16:03.2600000 | 2012-08-18 10:16:03.2501680 | -10 |
| 2012-08-18 10:16:03.2630000 | 2012-08-18 10:16:03.2501680 | -13 |

此差异太大，无法成为舍入问题，并且不能只是时间问题，调用两个函数之间会有延迟，因为该问题存在于GETDATE报告10:16:03.26X而SYSDATETIME的多行中>报告10:16:03.250

This discrepancy is too large to be a rounding issue and can't just be a timing issue with a delay between calling the two functions as the issue exists on more than one row that GETDATE reports 10:16:03.26X whereas SYSDATETIME reports 10:16:03.250

这篇关于为什么GETDATE()和SYSDATETIME()之间的Datediff总是不同的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！