SQL Server中where子句datetime条件匹配异常 如何实现精确查询

问题原因 #

这是SQL Server datetime 类型的固有精度特性导致的：datetime 类型的时间精度为 1/300 秒（约3.33毫秒），存储时会自动将毫秒值舍入到以0、3、7结尾的刻度。所以你给出的三个时间字符串转换为 datetime 类型后，都会被舍入为 2021-11-01 08:51:56.123，自然三条查询都能匹配到同一条记录。

解决方案 #

方案1（推荐）：更换字段类型为datetime2

datetime2是SQL Server 2008及之后版本推出的日期类型，支持自定义精度，毫秒级匹配可以选择精度为3的datetime2(3)，它会精确存储到毫秒位，不会做3.33毫秒的舍入。
操作步骤：

• 调整表字段类型（如果字段有索引、非空约束需要先临时移除，修改完成后再恢复）：

ALTER TABLE cmd ALTER COLUMN [timestamp] datetime2(3) NOT NULL

• 调整后直接做等值查询即可，只有毫秒完全匹配的记录会被返回：

select * from cmd where [timestamp] = convert(datetime2(3), '2021-11-01 08:51:56.123')

方案2：不修改表结构，查询时做精度转换

如果不方便修改表结构，可以在查询时将两侧的值都转换为datetime2(3)再比对：

select * from cmd where convert(datetime2(3), [timestamp]) = convert(datetime2(3), '2021-11-01 08:51:56.123')

注意点 #

timestamp是SQL Server的保留关键字，作为字段名使用时建议始终用方括号包裹，避免语法报错。

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