为什么从DATETIME转换为DATETIME2似乎会改变值？ [英] Why does conversion from DATETIME to DATETIME2 appear to change value?

问题描述

我有一个比较两个日期的存储过程。从我的应用程序逻辑来看，我希望它们是相等的。但是，比较失败。这样做的原因是，其中一个值存储为 DATETIME ，并且必须进行 CONVERT -ed到 DATETIME2 之前，先与其他 DATETIME2 进行比较。显然，这改变了它的价值。我已经运行了这个小测试：

  DECLARE @DateTime DATETIME ='2018-01-18 16：12：25.113'
 DECLARE @ DateTime2 DATETIME2 ='2018-01-18 16：12：25.1130000'
选择@ DateTime，@ DateTime2，DATEDIFF（NANOSECOND，@ DateTime，@ DateTime2）
  

哪个给了我以下结果：

为什么这些值之间有333333ns的差异？我认为 DATETIME2 作为更精确的类型，应该能够准确表示可以存储在 DATETIME ？

解决方案

A 重大更改，用于转换和比较datetime和datetime2。在此知识库文章。

总而言之，在SQL 2014和更早版本的转换过程中，对值进行了四舍五入，而如今已考虑了全精度。这样可以提高性能，但在转换和比较这些不同类型时会带来问题。

I had a stored procedure comparing two dates. From the logic of my application, I expected them to be equal. However, the comparison failed. The reason for this was the fact that one of the values was stored as a DATETIME and had to be CONVERT-ed to a DATETIME2 before being compared to the other DATETIME2. Apparently, this changed its value. I have run this little test:

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2='2018-01-18 16:12:25.1130000'
SELECT @DateTime, @DateTime2, DATEDIFF(NANOSECOND, @DateTime, @DateTime2)

Which gave me the following result:

Why is there the difference of 333333ns between these values? I thought that a DATETIME2, as a more precise type, should be able to accurately represent all the values which can be stored in a DATETIME? The documentation of DATETIME2 only says:

When the conversion is from datetime, the date and time are copied. The fractional precision is extended to 7 digits.

No warnings about the conversion adding or subtracting 333333ns to or from the value! So why does this happen?

I am using SQL Server 2016.

edit: Strangely, on a different server I am getting a zero difference. Both are SQL Server 2016 but the one where I have the problem has compatibility level set to 130, the one where I don't has it set to 120. Switching between them changes this behaviour.

edit2: DavidG suggested in the comments that the value I am using can be represented as a DATETIME2 but not a DATETIME. So I have modified my test to make sure that the value I am assigning to @DateTime2 is a valid DATETIME value:

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2=CONVERT(DATETIME2, @DateTime)
SELECT @DateTime, @DateTime2, DATEDIFF(NANOSECOND, @DateTime, @DateTime2)

This helps a little because the difference is smaller but still not zero:

解决方案

A breaking change was introduced in SQL Server 2016 with regards to conversion and comparison of datetime and datetime2. The changes are detailed in this knowledge base article.

In summary, values were rounded during the conversion in SQL 2014 and earlier versions whereas the full precision is considered nowadays. This improves performance but introduces issues when converting and comparing these unlike types.

这篇关于为什么从DATETIME转换为DATETIME2似乎会改变值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！