SQL Server不同版本datetimeoffset比较行为变更咨询
SQL Server datetime 与 datetimeoffset 跨版本比较的行为差异说明
核心差异来源
测试中观察到的两组查询的结果差异,本质是日期类数据类型的隐式转换优先级规则随兼容级别调整带来的行为变化,并非仅SQL Server 2019独有的变更。
首先对两组查询的固定行为做明确说明:
- 第一组直接拿datetime值和同格式字符串字面量比较的逻辑,在所有支持datetime类型的SQL Server版本中行为完全一致:因为
'2019-09-06 11:28:05.757'是datetime精度下可精确表示的时间值,test_date < 同值字符串的判断恒为假,所有版本下都会返回空集,不存在版本差异。 - 第二组跨版本的结果分叉,发生在datetime值和datetimeoffset参数的比较场景:
- SQL Server 2014及更早版本(兼容级别120及以下):隐式转换逻辑为高优先级的datetimeoffset向低优先级的datetime转换。由于示例中的@P1没有显式指定时区偏移量,赋值时会自动附加当前会话的本地时区偏移,在向datetime转换的过程中,偏移量会被直接丢弃,转换后得到的时间值和test_date完全相等,因此
<条件判断为假,返回空集。 - SQL Server 2016及之后版本(兼容级别130及以上,包含2019):微软调整了日期类型的优先级排序,明确datetimeoffset的优先级高于datetime。此时的隐式转换逻辑反过来:低优先级的datetime值会被自动转换为datetimeoffset类型,转换过程中会给原本无时区信息的datetime值附加当前会话的本地时区偏移。由于两类类型的精度差异——datetime的精度约为3.33毫秒,datetimeoffset的精度为100纳秒——转换后的datetimeoffset值会存在微小的刻度偏移,最终使得原相等值的
<判断成立,返回对应匹配行。
- SQL Server 2014及更早版本(兼容级别120及以下):隐式转换逻辑为高优先级的datetimeoffset向低优先级的datetime转换。由于示例中的@P1没有显式指定时区偏移量,赋值时会自动附加当前会话的本地时区偏移,在向datetime转换的过程中,偏移量会被直接丢弃,转换后得到的时间值和test_date完全相等,因此
常见规避方案
- 如需保持旧版本的比较逻辑,可在比较条件中显式完成类型转换,例如将条件写为
where test_date < CONVERT(datetime, @P1),避免隐式转换规则的干扰。 - 如需保持时区计算的一致性,可在给datetimeoffset变量赋值时显式指定固定时区偏移(例如
set @P1 = '2019-09-06 11:28:05.757 +08:00'),同时对datetime列的转换指定相同的偏移规则,避免会话默认时区带来的结果波动。 - 升级数据库时可检查当前兼容级别,若将兼容级别从120提升到130及以上,上述隐式转换的行为变更就会自动生效,可提前排查存量依赖隐式转换逻辑的查询,避免上线后出现非预期的结果差异。
内容的提问来源于stack exchange,提问作者Ashu
相关产品推荐
相关产品推荐

