为什么从DATETIME转换为DATETIME2似乎会改变值？

Question

我有一个存储过程用于比较两个日期。根据我的应用逻辑，我希望它们相等。然而，比较失败了。原因是其中一个值作为 DATETIME 存储，需要在与另一个 DATETIME2 比较之前进行 CONVERT 转换。显然，这会改变其值。我运行了这个小测试：

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)

给出以下结果： 为什么这些值之间会有333333纳秒的差异？我以为 DATETIME2 作为更精确的类型，应该能够准确地表示在 DATETIME 中可以存储的所有值吗？DATETIME2 的文档 只是说：

当转换来自 datetime 时，日期和时间被复制。小数位精度扩展到 7 位。

没有关于转换会将333333纳秒加入或从值中减去的警告！那么为什么会发生这种情况呢？

我正在使用 SQL Server 2016。

编辑：奇怪的是，在另一台服务器上我得到了零差异。两者都是SQL Server 2016，但存在问题的服务器兼容级别设置为130，而没有问题的服务器兼容级别设置为120。在它们之间切换会改变这种行为。

编辑2：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)

这有点帮助，因为差异更小了，但仍不为零：

- kamilk

---

这是浮点数精度问题。2018-01-18 16:12:25.113 这个值无法在 datetime 值中表示。 - DavidG

我正在使用SQL Server 2014，但无法复现；我从datediff中得到了0。 - HoneyBadger

@HoneyBadger，请查看我有关兼容级别的编辑。 - kamilk

Answer 1

在SQL Server 2016中，涉及日期时间和日期时间2的转换和比较出现了一项“重大变更”（breaking change）。具体细节请参考此知识库文章。

简而言之，在SQL 2014及以前版本的转换过程中会进行四舍五入，而现在则考虑完整精度。这样可以提高性能，但当转换和比较这些不同类型时也会引入问题。

- Dan Guzman

---

谢谢，我需要更仔细地阅读，但这篇文章似乎准确描述了我正在遇到的问题。并且它解释了为什么降低兼容性级别会有所帮助。 - kamilk

Answer 2

根据这篇MSDN博客文章，DATETIME精度为0.00333秒，而DATETIME2（或显式的DATETIME2（7））精度为100纳秒。因此，即使将DATETIME与似乎具有相同精度的DATETIME2（3）进行比较，DATETIME2（3）也更加精确。

DATETIME的这种奇怪的3.33毫秒精度是在比较看似相等的值时出现差异的原因。

- Justinas Marozas

Answer 3

datetime2是datetime2(7)的简写，表示您希望使用7位小数位（最大值）。如果您想要接近datetime的时间精度，请尝试使用datetime2(3)。

此外，请注意datetime2(3)比datetime更加精确。后者按设计四舍五入到最近的0.000、0.003或0.007。

- Bacon Bits

Answer 4

在进行比较时，实际上应该将高精度数据转换为低精度数据，以避免出现“差异”。

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

结果如下：

结果是

- jyao