Incorrect datetime value ‘2021-02‘ for function str_to_date

缺乏、安全感 2022-10-29 05:21 186阅读 0赞

在使用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:

![20210208185525303.png][]

（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

![20210208185624477.png][]

**问题原因：**

产生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：

![20210208185811106.png][]

更新语句报错：

![20210208185832554.png][]

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

![20210208185858804.png][]

更新语句报错：

![20210208185923439.png][]

（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语句

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70][]

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

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

![20210208190137478.png][]

返回值不再是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', '');

[20210208185525303.png]: /images/20221024/e5ce0cc3b3bc4335b775c358a3decb3a.png
[20210208185624477.png]: /images/20221024/c15cefc66959454591857f29aed0bbd9.png
[20210208185811106.png]: /images/20221024/f111c3edbd054288b116c1d59b6c90c2.png
[20210208185832554.png]: /images/20221024/fbf8b2f1e23040c781a1626f37f239a1.png
[20210208185858804.png]: /images/20221024/730e2ebed41f4a6ea8e900871ef31a1e.png
[20210208185923439.png]: /images/20221024/470a5663e05c4983a5234c627a2d1ed6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70]: /images/20221024/5e7945d3c9eb410d908bb3aeb4cca6e3.png
[20210208190137478.png]: /images/20221024/0ed7d127881b417093696ccaab2fb6a5.png