SQL Server中yyyy-mm-ddThh:mm:ss.sssz格式日期筛选异常问题

问题背景 #

在SQL Server中使用yyyy-mm-ddThh:mm:ss.sssz格式的日期字符串进行毫秒级筛选时，出现结果不符合预期的情况：
执行以下查询：

select createdon from tableName where createdon<='2023-11-20T13:19:59.044'
select createdon from tableName where createdon<='2023-11-20T13:19:59.045'
select createdon from tableName where createdon<='2023-11-20T13:19:59.046'
select createdon from tableName where createdon<='2023-11-20T13:19:59.047'
select createdon from tableName where createdon<='2023-11-20T13:19:59.048'

已知表中createdon的值均大于这些筛选条件，但第二条和第三条查询仍返回了结果。

核心原因 #

SQL Server的datetime类型存在3.33毫秒的精度限制，无法精确存储所有毫秒值。当传入像045、046这类毫秒数时，SQL Server会自动将其舍入到最近的可存储值（比如043或047）。这就导致原本应该不匹配的筛选条件，因舍入后的值与表中实际存储的时间产生匹配，从而返回非预期结果。

如果使用的是datetime2类型（精度最高7位小数），若筛选时未显式指定转换精度或存在隐式转换，也可能出现类似的精度匹配偏差。

解决方案 #

1. 显式转换为匹配精度的datetime2类型 #

如果createdon字段是datetime2类型，将筛选字符串显式转换为对应精度的datetime2，避免隐式转换的精度损失：

select createdon from tableName where createdon <= CAST('2023-11-20T13:19:59.045' AS datetime2(3))

datetime2(3)指定3位毫秒精度，与筛选字符串的精度完全匹配。

2. 适配datetime类型的精度限制 #

如果字段是datetime类型，需要调整筛选条件到SQL Server可精确存储的毫秒值，或者使用范围查询规避舍入问题：

-- 替代<= '2023-11-20T13:19:59.045'，查询小于下一个可存储的时间点
select createdon from tableName where createdon < '2023-11-20T13:19:59.047'

3. 验证实际存储的时间值 #

先查询表中createdon的精确存储值，确认其毫秒级的具体数值：

SELECT CONVERT(varchar(23), createdon, 121) AS createdon_exact FROM tableName

使用CONVERT的121样式可显示到3位毫秒的精确格式，帮助你确认实际存储的时间，避免对数据的误判。

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