sql - 为什么从 DATETIME 到 DATETIME2 的转换似乎会改变值？

Question

我有一个比较两个日期的存储过程。从我的应用程序的逻辑来看，我希望它们是相等的。然而，比较失败了。原因是其中一个值存储为 DATETIME，并且在被转换之前必须将 CONVERT 转换为 DATETIME2与其他 DATETIME2 相比。显然，这改变了它的值(value)。我运行了这个小测试:

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)

这给了我以下结果:

为什么这些值之间存在 333333ns 的差异？我认为DATETIME2作为一种更精确的类型，应该能够准确地表示可以存储在DATETIME中的所有值？ The documentation of DATETIME2只说:

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

没有关于在值中添加或减去 333333ns 的转换的警告!那么为什么会发生这种情况呢？

我使用的是 SQL Server 2016。

编辑:奇怪的是，在不同的服务器上我得到的差异为零。两者都是 SQL Server 2016，但我遇到问题的那个将兼容性级别设置为 130，而我没有问题的那个则将兼容性级别设置为 120。在它们之间切换会更改此行为。

edit2: DavidG 在评论中建议我使用的值可以表示为 DATETIME2，但不能表示为 DATETIME。因此，我修改了测试，以确保分配给 @DateTime2 的值是有效的 DATETIME 值:

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

这有一点帮助，因为差异较小但仍然不为零:

Answer 1

一个breaking change SQL Server 2016中引入了有关datetime和datetime2的转换和比较的功能。这些变化详见 this knowledge base article 。

总而言之，在 SQL 2014 及更早版本中，值在转换过程中进行了舍入，而现在则考虑完整精度。这提高了性能，但在转换和比较这些不同类型时引入了问题。