SQL插入数据时str_to_date转换'3/6/2009'报日期值不正确如何解决？

问题原因 #

报错的核心原因是STR_TO_DATE函数的格式参数和输入的日期字符串格式不匹配：

• 输入的日期字符串为3/6/2009，采用斜杠/作为分隔符，顺序为月/日/年
• 你使用的格式参数为%m-%d-%Y，采用横杠-作为分隔符，二者规则不一致，导致无法解析为合法日期值
  STR_TO_DATE函数要求格式参数和输入字符串的分隔符、各部分顺序必须完全对应才能正确解析

解决方法 #

方法1：修改格式参数匹配输入的日期字符串 #

把STR_TO_DATE里的格式占位符的分隔符改成斜杠即可，修改后的插入语句如下：

insert into movie
(movieid, title, genreid, releasedate, rating, movielength, directorid)
values
('1', 'Watchmen', '10',str_to_date('3/6/2009','%m/%d/%Y'), '4.4', '2h43m', '1021');

方法2：修改日期字符串的分隔符匹配现有格式参数 #

如果要保留%m-%d-%Y的格式规则，就把输入的日期字符串的斜杠改成横杠：

insert into movie
(movieid, title, genreid, releasedate, rating, movielength, directorid)
values
('1', 'Watchmen', '10',str_to_date('3-6-2009','%m-%d-%Y'), '4.4', '2h43m', '1021');

方法3：直接使用SQL标准日期格式 #

不需要调用STR_TO_DATE函数，直接用'YYYY-MM-DD'格式的字符串赋值给日期字段即可，兼容性更强：

insert into movie
(movieid, title, genreid, releasedate, rating, movielength, directorid)
values
('1', 'Watchmen', '10','2009-03-06', '4.4', '2h43m', '1021');

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