Incorrect datetime value ‘2021-02‘ for function str_to_date

在使用MySQL的STR_TO_DATE函数将字符串转换成Date类型的时候，会出现类似：Incorrect datetime value '2021-02' for function str_to_date的错误。下面来看看解决办法。

 

情景复现：

（1）创建表：

create table time_table(
`year` varchar(4),
`month` varchar(2),
`day` varchar(2)
);

（2）插入数据：

INSERT INTO time_table
(`year`, `month`, `day`)
VALUES('2021', '03', '02');

（3）查询数据：

select STR_TO_DATE(CONCAT(`year`,'-',`month`),'%Y-%m') from time_table;

执行语句不发生错误，但返回的结果为NULL:

（4）更新数据：

update time_table set month = '04' where  STR_TO_DATE(CONCAT(`year`,'-',`month`),'%Y-%m') > CURRENT_DATE();

执行更新语句产生错误：Incorrect datetime value: '2021-03' for function str_to_date

问题原因：

产生Incorrect datetime value: '2021-03' for function str_to_date报错的原因是对STR_TO_DATE函数的使用不当。STR_TO_DATE(参数1, 参数2)，其中参数1必须是年月日格式的字符串，比如：'2021-03-02'。因为CONCAT(`year`,'-',`month`)语句得到的字符串只含有年月，缺少了日，不是正确的年月日格式，因此会提示错误。对于参数2是日期格式，形式必须和参数1保持一致，即如果参数1为'2021-03-02'，那么参数2需要为'%Y-%m-%d'。如果参数1为'2021/03/02'，那么参数2需要为'%Y/%m/%d'。

 参数1和参数2格式必须一致，该特点和Java中的SimpleDateFormat特点一致，比如：

// 对应STR_TO_DATE的参数2
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-mm");
// 对应STR_TO_DATE的参数1
Date date = simpleDateFormat.parse("2021/03/02");
System.out.println(date);

运行该代码，会报错：

Exception in thread "main" java.text.ParseException: Unparseable date: "2021/03/02"

（1）比如如下参数1（使用斜杠分割）和参数2（使用短杠分割）格式不一致，查询语句结果为NULL：

更新语句报错：

（2）再比如如下参数1是年月日格式，参数2是年月表达式，查询语句仍然返回NULL：

更新语句报错：

（3）比如如下查询语句，参数1和参数2格式一致，都可以返回结果：

select STR_TO_DATE(CONCAT(`year`,'-',`month`, '-', `day`),'%Y-%m-%d') from time_table;
select STR_TO_DATE(CONCAT(`year`,'/',`month`, '/', `day`),'%Y/%m/%d') from time_table;

如下更新语句都可以正常执行，不提示错误：

update time_table set month = '04' where  STR_TO_DATE(CONCAT(`year`,'-',`month`, '-', `day`),'%Y-%m-%d') > CURRENT_DATE();
update time_table set month = '04' where  STR_TO_DATE(CONCAT(`year`,'/',`month`, '/', `day`),'%Y/%m/%d') > CURRENT_DATE();

解决办法：

如果一定要使用STR_TO_DATE(CONCAT(`year`,'-',`month`),'%Y-%m')这样的格式，也是有解决办法的。

（1）查看SQL_MODE

select @@SQL_MODE

会在返回结果中看见有个内容：NO_ZERO_DATE。

出现上述报错的原因，就是这个模式的作用结果。我们可以将该模式移除，便可以解决报错问题。

（2）移除NO_ZERO_DATE

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');

将SQL_MODE中的NO_ZERO_DATE内容替换成空字符串，即可将模式移除。

（3）再执行前面的UPDATE语句

语句顺利执行，并且数据也被改变。

（4）再执行前面的SELECT语句

返回值不再是NULL，而是有内容了。但是内容除了年月，还包含了日，而且日期是2021-04（经过UPDATE语句修改后的数据库值）的前一个月的最后一天。

 

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');

该语句移除NO_ZERO_DATE模式，仅仅在当前会话内有效。当你断开当前的数据库连接，再重新连接，此时该模式恢复，执行UPDATE语句仍然会报错。

全局修改的方式是添加GLOBAL，这样修改的方式在断开重新连接以后，修改仍然有效。但是如果重启MySQL服务之后就会恢复到原先的设置。

SET GLOBAL SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');