MySQL迁移至SQL Server 2019遇日期转换越界问题求SQL Server端方案

错误原因 #

你遇到的错误核心是日期格式解析不匹配，和字符编码无关。SQL Server默认的日期解析格式如果是mdy（月-日-年），而你的应用传入的是Y-m-d（年-月-日）格式的日期字符串（比如2022-12-21 14:30:18.864），SQL Server会错误地把12当成日、21当成月，而月份最大为12，因此触发"超出范围"的转换错误。

SQL Server端解决方案 #

不需要修改字符编码，通过调整SQL Server的日期格式设置即可彻底解决，无需修改大量应用代码：

1. 临时会话级修改（即时生效，重启连接后失效） #

在执行插入操作前，先运行以下SQL语句，指定当前会话按年-月-日解析日期：

SET DATEFORMAT ymd;

之后再执行你的插入语句，就能正确解析日期字符串。

2. 永久修改数据库默认格式（长期有效） #

日期格式和数据库的默认语言绑定，你可以修改数据库的默认语言为支持ymd格式的语言：

• 查看当前数据库的默认语言：

SELECT name, default_language_name FROM sys.databases WHERE name = '你的数据库名称';

• 修改数据库默认语言为简体中文（对应日期格式为ymd）：

ALTER DATABASE 你的数据库名称 SET DEFAULT_LANGUAGE = '简体中文';

如果需要保留英文环境，也可以设置为British English，它的默认日期格式同样是ymd：

ALTER DATABASE 你的数据库名称 SET DEFAULT_LANGUAGE = 'British English';

3. 修改登录账号的默认语言（对该账号所有连接生效） #

如果不想修改数据库全局设置，可以调整特定登录账号的默认语言：

ALTER LOGIN 你的登录账号名 WITH DEFAULT_LANGUAGE = '简体中文';

验证 #

完成上述设置后，重新执行报错的插入语句（注意字符串值需加单引号）：

insert into [roles] ([name], [guard_name], [updated_at], [created_at]) values ('AgentControlleur', 'api', '2022-12-21 14:30:18.864', '2022-12-21 14:30:18.864')

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