SQL Server数据库BAK还原迁移后varchar转datetime出现超出范围错误

报错原因 #

• 核心问题是会话级日期格式（DATEFORMAT）配置不匹配：你写的'30/09/2021 00:52:14'是日/月/年（DMY）格式，而新SQL Server 2019实例的会话默认DATEFORMAT是月/日/年（MDY），转换时会把30识别为月份，超出月份1-12的合法范围，因此抛出转换越界错误。
• 补充说明：你之前验证的排序规则、数据库包含性都属于数据库级配置，而DATEFORMAT默认值由实例级默认语言设置、当前登录用户的默认语言配置决定，不属于数据库级配置，因此备份还原不会同步该配置，这就是新旧环境行为不一致的根本原因。

解决方案 #

方案1：修改SQL写法，使用不依赖环境配置的日期格式（最推荐，一劳永逸） #

SQL Server中有两类字符串转datetime的格式完全不依赖DATEFORMAT设置，不会受环境配置影响：

1. ISO8601格式：YYYY-MM-DDTHH:MM:SS，修改后的SQL如下：

SELECT userID FROM tblLogin 
WHERE CAST('2021-09-30T00:52:14' AS datetime) < DATEADD(n,600,accessDate)

2. 无分隔符的纯数字日期格式：YYYYMMDD HH:MM:SS，比如'20210930 00:52:14'，转换同样不受环境配置影响。

方案2：修改新实例的默认DATEFORMAT配置 #

如果不想修改历史SQL代码，可以统一调整新环境的日期格式为DMY：

• 实例全局配置：打开SQL Server Management Studio -> 右键实例 -> 属性 -> 高级 -> 默认语言，修改为支持DMY格式的语言（比如英式英语），修改后重启实例生效。
• 单用户配置：如果只需要特定登录用户使用DMY格式，右键对应登录名 -> 属性 -> 常规 -> 默认语言，修改后该用户新建立的会话自动生效。
• 会话级临时配置：在查询开头加SET DATEFORMAT DMY;，仅当前会话生效，适合临时验证用：

SET DATEFORMAT DMY;
SELECT userID FROM tblLogin 
WHERE CAST('30/09/2021 00:52:14' AS datetime) < DATEADD(n,600,accessDate)

方案3：使用CONVERT函数显式指定格式 #

如果需要保留原来的日期字符串写法，可以用CONVERT函数指定格式编码，103对应DMY格式的编码：

SELECT userID FROM tblLogin 
WHERE CONVERT(datetime, '30/09/2021 00:52:14', 103) < DATEADD(n,600,accessDate)

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