SQL v14.0.1000.169中DateTime转DateTime2舍入导致等值比较失败问题

• datetime和datetime2的底层存储精度规则完全不同：datetime类型的固定精度为3.33毫秒，毫秒部分只能以0.000、0.003、0.007三个值收尾，你查询结果中看到datetime显示为13:50:11.157只是客户端格式化后的展示值，实际存储的近似值转换为datetime2后为13:50:11.1566667
• SQL Server存在隐式类型转换优先级规则：datetime2的优先级高于datetime，二者做等值比较时，系统会自动把datetime类型隐式转换为datetime2类型，转换后暴露的实际存储近似值和原本精确的datetime2值13:50:11.1570000存在差异，所以等值判断结果为不相等。

• 方案1：统一业务字段的时间类型，全量改用datetime2，避免两种时间类型混用，从根源消除精度差异问题。
• 方案2：等值比较时显式统一转换精度，比如统一转换为精度为3的datetime2：

IF CONVERT(datetime2(3), @dt) = CONVERT(datetime2(3), @dt2)
PRINT 'The Same (Explicit Convert)'

也可以统一转换为datetime类型：

IF @dt = CONVERT(datetime, @dt2)
PRINT 'The Same (Explicit Convert)'

• 方案3：用时间差阈值判断替代等值判断，只要二者差值小于业务可接受的精度范围就判定为相等，示例如下：

IF DATEDIFF(MILLISECOND, @dt, @dt2) < 1
PRINT 'The Same (Threshold Judgment)'

内容的提问来源于stack exchange，提问作者Tyler Antoni