SSMS中DATEADD函数识别错误区域日期格式问题咨询

问题成因 #

该问题不是DATEADD函数的已知缺陷，也和SSMS v17.9.1版本无关，根源是SQL Server对非明确格式的字符串日期字面量的解析规则受当前会话DATEFORMAT参数、数据库排序规则关联的区域设置影响。

很多开发者默认yyyy-mm-dd是通用安全的日期格式，但实际上对于SQL Server的datetime/smalldatetime类型来说，该格式并不具备区域安全性：当会话DATEFORMAT被设置为dmy时，就会将'2021-09-01'按年-日-月规则解析为2021年1月9日，再加7天就会得到你遇到的2021-01-16 00:00:00.000错误结果。
其他功能可以正常识别该格式，大概率是因为对应场景使用了参数化查询、或者操作对象本身就是日期类型字段，没有触发字符串字面量解析逻辑。

可用解决方案 #

• 使用区域安全的字符串日期格式
  SQL Server全版本、全日期类型通用的安全字符串格式有两种，不受任何区域设置影响：
  • 无分隔符8位格式：'YYYYMMDD'，示例写法：DATEADD(day, 7, '20210901')
  • 带T的ISO8601格式：'YYYY-MM-DDTHH:MM:SS'，示例写法：DATEADD(day, 7, '2021-09-01T00:00:00')
• 显式指定日期转换格式
  用CONVERT函数指定样式码明确解析规则，yyyy-mm-dd对应的样式码为23，示例写法：
  DATEADD(day, 7, CONVERT(DATE, '2021-09-01', 23))
  该方案可读性高，不会影响其他业务逻辑，是最推荐的处理方式。
• 调整会话DATEFORMAT设置（不推荐）
  可执行SET DATEFORMAT ymd;修改当前会话的日期解析规则，该设置仅对当前会话生效，容易遗漏设置导致其他逻辑异常，不建议作为常规解决方案。

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