从DATETIME2到DATETIME的意外舍入 [英] Unexpected rounding-off from DATETIME2 to DATETIME

问题描述

首先，它可能没有实际用途但只是想知道如何/为何发生这种情况。



当我尝试分配DATETIME2时值为DATETIME变量时，值会发生意外的舍入。



示例SQL：

< pre lang =SQL> DECLARE @ DT DATETIME
DECLARE @ DT2 DATETIME2

SET @ DT2 = ' 2015-07-23 18：35：34.8815038 '
SELECT @ DT = @ DT2

SELECT @ DT2 AS [DATETIME2]， @ DT AS [ DATETIME ]

输出：

  DATETIME2 DATETIME  
 2015-07-23 18：35：34.8815038 | 2015-07-23 18：35：34.883 

现在，我无法找到原因 8815038 881或<8> 毫秒，i>纳秒被舍入到 883 毫秒。



如果任何机构知道对此的确切解释，请在此处分享。 ：）

解决方案

Datetime2比datetime更准确，只有毫秒精度。



来自< a href =https://msdn.microsoft.com/en-us/library/bb677335.aspx> datetime2（Transact-SQL） [ ^ ]：

当小数精度时如果datetime2（n）值大于三位数，则该值将被截断。

以下示例显示将datetime2（4）值转换为日期时间值的结果。

  DECLARE   @ datetime2  datetime2（ 4 ）= '  1968-10-23 12：45：37.1237'; 
  DECLARE   @ datetime   datetime  =  @ datetime2 ; 
 
  SELECT   @ datetime   AS  '  @ datetime'， @ datetime2   AS  '  @ datetime2' ; 
 
   -   结果 
   -    @ datetime @ datetime2  
   -    ----------------------- -------------------- ----  
   -    1968-10-23 12：45：37.123 1968- 10-23 12：45：37.1237  
   -     
   -   （1行受影响） 

由于datetime的准确性，这种情况发生的值不是881毫秒。日期时间在毫秒级别上不准确，但准确度四舍五入到.000，.003或.007秒的增量;



您可以通过一个小循环显示：

  DECLARE   @ DT   DATETIME  
  DECLARE   @ DT2  DATETIME2 
  DECLARE   @ counter   INT  = < span class =code-digit> 0  
 
  SET  @ DT2 =  '  2015-07-23 18：35：34.8815038' 
 
  WHILE   @ counter <  10   BEGIN  
  SELECT  @ DT = @ DT2 
  SELECT   @ DT2   AS  [DATETIME2]， @ DT   AS  [ DATETIME ]; 
  SET   @ DT2  = DATEADD（ms， 1 ， @ DT2 ）
  SET   @counter  =  @ counter  +  1  
  END  

有关详细信息，请参阅 datetime（Transact-SQL） [ ^ ]

我来了 解决方案。我在 Stackoverflow 中发布了相同的问题[ ^ ]

Quote：

原因是在datetime类型中，你只有.000，.003和.007分数。

试试这个：

 选择 cast（'  2015-01-01 00：00：00.882'  as   datetime  ）
 选择 cast（'  2015-01 -01 00：00：00.884'  as   datetime ）

你会看到两者都四舍五入到.883

日期时间的精度四舍五入到.000，.003或.007秒的增量。



参考：

https://msdn.microsoft.com/en-us/library/ms187819.aspx [ ^ ]

First of all, it may not have a practical use but just want to know how/why this is happening.

When I am trying to assign a DATETIME2 value to a DATETIME variable, there happens an unexpected roundoff in the value.

Sample SQL :

DECLARE @DT DATETIME
DECLARE @DT2 DATETIME2

SET @DT2='2015-07-23 18:35:34.8815038'
SELECT @DT=@DT2

SELECT @DT2 AS [DATETIME2],@DT AS [DATETIME]

OUTPUT :

DATETIME2	                         DATETIME               
2015-07-23 18:35:34.8815038    |     2015-07-23 18:35:34.883

Now, I am not able to find the cause why 8815038 nano seconds is rounded off to 883 milliseconds against expected 881 or 882 milliseconds.

If any body knows the exact explaination to this, please share here. :)

解决方案

Datetime2 is more accurate than datetime which has only accuracy for milliseconds.

From datetime2 (Transact-SQL)[^]:

When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated.
The following example shows the results of converting a datetime2(4) value to a datetime value.

DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';
DECLARE @datetime datetime = @datetime2;

SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';

--Result
--@datetime               @datetime2
------------------------- ------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237
--
--(1 row(s) affected)

What comes to the value not being 881 milliseconds this happens because of the accuracy of datetime. Datetime is not accurate on millisecond level but Accuracy is rounded to increments of .000, .003, or .007 seconds;

You can visualize this with a small loop:

DECLARE @DT DATETIME
DECLARE @DT2 DATETIME2
DECLARE @counter INT = 0
 
SET @DT2='2015-07-23 18:35:34.8815038'

WHILE @counter < 10 BEGIN
   SELECT @DT=@DT2
   SELECT @DT2 AS [DATETIME2],@DT AS [DATETIME];
   SET @DT2 = DATEADD(ms, 1, @DT2)
   SET @counter = @counter + 1
END

For more information, see datetime (Transact-SQL)[^]

Here I got the solution. I have posted the same question in Stackoverflow[^]

Quote:

The reason is that in datetime type, you only have .000, .003 and .007 fraction of a second.

Try this:

select cast('2015-01-01 00:00:00.882' as datetime)
select cast('2015-01-01 00:00:00.884' as datetime)

And you'll see that both are rounded to .883
Accuracy of datetime is rounded to increments of .000, .003, or .007 seconds.

Reference:
https://msdn.microsoft.com/en-us/library/ms187819.aspx[^]

这篇关于从DATETIME2到DATETIME的意外舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！