Mysql – Incorrect value for str_to_date

date formatMySQL

I'm running a simple procedure that reads from a VARCHAR column and inserts data into a TIME column, using this format:

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

I'm getting the error

SQL Error (1411): Incorrect datetime value: '09:22' for function str_to_date

I find that a similar statement, SELECT STR_TO_DATE("09:22", "%H:%i") gives me a NULL when I run it from a HeidiSQL client.

I'm sure this syntax used to work and I'm not aware of having changed any settings, can you suggest what I've done wrong?

Edit: Just remembered I have made a recent change, I installed a WordPress database on the same server, might that have had some affect?

Here's what get running on the server:

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

Best Answer

This looks like a bug (though it isn't technically a bug, see my first edit towards the end of the answer):

I see the behaviour on MySQL 5.7.20 and 5.7.21 (on Centos 7.4 if it matters):

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)

And the warning is code 1411 Incorrect datetime value: '09:22' for function str_to_date.

ANSI_QUOTES is not enabled, and I get the same warning if I use single-quotes.

From what I understand reading the documentation, the query should work:

STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

This query, containing a date as well as a time part, does work: 

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)

Running the original query on MariaDB 10.2.14 works correctly:

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)

EDIT: This was reported as a bug previously, see here: https://bugs.mysql.com/bug.php?id=80064

If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates or part of dates are disallowed. In that case, STR_TO_DATE() returns NULL and generates a warning:

These modes are enabled by default in MySQL 5.7 (but not in MariaDB 10.2). So, after removing these from the sql_mode system variable, we get the desired result:

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)

EDIT 2: We don't want to accidentally remove helpful/important modes from the sql_mode system variable, so ideally we should only remove specific modes, in this case NO_ZERO_DATE and NO_ZERO_IN_DATE. This can be done this way:

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

(Use SET GLOBAL sql_mode... if you want to change it for the global scope rather than just the session.)

If you want to set sql_mode in the MySQL config file, then I think you'll just have to "hard-code" it, i.e. just find out what the default modes are for the version you're using, remove the undesired modes, and add the resulting string e.g. to the [mysqld] section of the .cnf file:

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

This should be correct for MySQL 5.7. When upgrading to the next version, you'll have to update the setting again.

Note that stored procedures run with their own sql_modes:

MySQL stores the sql_mode system variable setting in effect when a routine is created or altered, and always executes the routine with this setting in force, regardless of the current server SQL mode when the routine begins executing.

Source: the MySQL stored procedure documentation.

You can also change the sql_mode within a stored procedure.

Related Question