主页 / dba / 问题 / 202627
Accepted
user147997
Asked: 2018-03-30 05:48:47 +0800 CST

str_to_date 的值不正确

  • 772

我正在运行一个简单的过程，它使用以下格式从VARCHAR列中读取数据并将数据插入到列中：TIME

STR_TO_DATE(vTestTime, '%H:%i').

我收到错误

SQL 错误 (1411)：日期时间值不正确：函数 str_to_date 的“09:22”

SELECT STR_TO_DATE("09:22", "%H:%i")当我从 HeidiSQL 客户端运行它时，我发现类似的语句给了我一个 NULL。

我确定这种语法曾经有效，并且我不知道更改了任何设置，您能建议我做错了什么吗？

编辑：刚刚记得我最近做了一个更改，我在同一台服务器上安装了一个 Wordpress 数据库，这可能会产生一些影响吗？

这是在服务器上运行的内容：

mysql> select str_to_date("09:22", "%H:%i");

+-------------------------------+

| str_to_date("09:22", "%H:%i") |

+-------------------------------+

| NULL                          |

+-------------------------------+

1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '09:22' for function str_to_date
mysql date-format

2 个回答

  1. Best Answer

    这看起来像一个错误（虽然它在技术上不是一个错误，请参阅我在答案末尾的第一次编辑）：

    我看到 MySQL 5.7.20 和 5.7.21 上的行为（如果重要的话，在 Centos 7.4 上）：

    mysql> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

    警告是代码 1411 Incorrect datetime value: '09:22' for function str_to_date。

    ANSI_QUOTES 未启用，如果我使用单引号，我会收到相同的警告。

    根据我阅读文档的理解，查询应该有效：

    如果格式字符串同时包含日期和时间部分，则 STR_TO_DATE() 返回 DATETIME 值；如果字符串仅包含日期或时间部分，则返回 DATE 或 TIME 值。

    这个包含日期和时间部分的查询确实有效：

    mysql> SELECT STR_TO_DATE("2017-03-17 09:22","%Y-%m-%d %H:%i");
+--------------------------------------------------+
| STR_TO_DATE("2017-03-17 09:22","%Y-%m-%d %H:%i") |
+--------------------------------------------------+
| 2017-03-17 09:22:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

    在 MariaDB 10.2.14 上运行原始查询可以正常工作：

    MariaDB [(none)]> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| 09:22:00                      |
+-------------------------------+
1 row in set (0.00 sec)

    编辑： 这是以前报告的错误，请参见此处： https ://bugs.mysql.com/bug.php?id=80064

    如果启用 NO_ZERO_DATE 或 NO_ZERO_IN_DATE SQL 模式，则不允许零日期或部分日期。在这种情况下，STR_TO_DATE() 返回 NULL 并生成警告：

    这些模式在 MySQL 5.7 中默认启用（但在 MariaDB 10.2 中不启用）。因此，在从 sql_mode 系统变量中删除这些之后，我们得到了想要的结果：

    mysql> SET sql_mode='';
mysql> select STR_TO_DATE("09:22", "%H:%i");
+-------------------------------+
| STR_TO_DATE("09:22", "%H:%i") |
+-------------------------------+
| 09:22:00                      |
+-------------------------------+
1 row in set (0.00 sec)

    编辑 2： 我们不想意外地从sql_mode系统变量中删除有用/重要的模式，所以理想情况下我们应该只删除特定的模式，在这种情况下为 NO_ZERO_DATE 和 NO_ZERO_IN_DATE。这可以通过以下方式完成：

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

    （SET GLOBAL sql_mode...如果您想为全局范围更改它而不仅仅是会话，请使用它。）

    如果您想sql_mode在 MySQL 配置文件中进行设置，那么我认为您只需要“硬编码”它，即只需找出您正在使用的版本的默认模式是什么，删除不需要的模式，然后例如，将生成的字符串添加到[mysqld].cnf 文件的部分：

    sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    这对于 MySQL 5.7 应该是正确的。升级到下一个版本时，您必须再次更新设置。

    请注意，存储过程使用自己的sql_modes 运行：

    MySQL 在创建或更改例程时存储有效的 sql_mode 系统变量设置，并始终使用此设置执行例程，而不管例程开始执行时当前服务器 SQL 模式如何。

    来源：MySQL 存储过程文档。

    您还可以sql_mode在存储过程中更改。

    • 3

  2. 我建议不要更新 sql_mode 只需在时间前面添加今天的日期，然后它就会起作用。

    问题：

    mysql> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

    解决方案：

    我的桌子是这样的。

    mysql> select * from restaurant_delivery_slots;
+----+---------------+-----------+------------+------------------+--------+
| id | restaurant_id | open_time | close_time | delivery_charges | status |
+----+---------------+-----------+------------+------------------+--------+
| 17 |         99997 | 7:00 AM   | 12:00 PM   |           150.00 |      1 |
| 18 |         99997 | 12:00 PM  | 6:00 PM    |           300.00 |      1 |
| 37 |           102 | 7:00 AM   | 12:00 PM   |           100.00 |      1 |
| 38 |           102 | 12:00 PM  | 5:00 PM    |           200.00 |      1 |
+----+---------------+-----------+------------+------------------+--------+
4 rows in set (0.00 sec)

    实施解决方案后，您将得到这样的结果。

    mysql> SELECT
    rds.id,
    rds.open_time,
    rds.close_time,
    STR_TO_DATE(CONCAT(CURDATE(),' ', rds.open_time), "%Y-%m-%d %l:%i %p") AS ot,
    STR_TO_DATE(CONCAT(CURDATE(),' ', rds.close_time), "%Y-%m-%d %l:%i %p") AS ct
    FROM restaurant_delivery_slots rds
    WHERE CONCAT(CURDATE(),' ', '10:49:01' ) BETWEEN STR_TO_DATE(CONCAT(CURDATE(),' ', rds.open_time), "%Y-%m-%d %l:%i %p") AND STR_TO_DATE(CONCAT(CURDATE(),' ', rds.close_time), "%Y-%m-%d %l:%i %p");
+----+-----------+------------+---------------------+---------------------+
| id | open_time | close_time | ot                  | ct                  |
+----+-----------+------------+---------------------+---------------------+
| 17 | 7:00 AM   | 12:00 PM   | 2022-09-06 07:00:00 | 2022-09-06 12:00:00 |
| 37 | 7:00 AM   | 12:00 PM   | 2022-09-06 07:00:00 | 2022-09-06 12:00:00 |
+----+-----------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

    欲了解更多信息，您可以联系我。

    • 0

相关问题