1

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
  • It should work: dbfiddle.uk/… – McNets Mar 29 '18 at 13:55
  • See if you have ANSI_QUOTES enabled. – mustaccio Mar 29 '18 at 14:53
  • No, ANSI_QUOTES is not enabled. – user147997 Mar 29 '18 at 17:24
2

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.

  • @user147997 Glad I could help. Obviously, it's not a good idea to just blank sql_mode like I did. Instead, just the two problematic modes should be removed. I'll update the answer to show this later ... – dbdemon Mar 30 '18 at 6:46
  • Thanks, I'd be interested in the correct way to do this. I added an "sql_mode=..." in the mysql.conf include file and restarted mysql. I found this fixed my interactive test using HeidiSQL and a simple select statement. But it didn't stop the stored procedure failing with the same original error. Finally I got it working by adding an "sql_mode=..." statement in the stored procedure. Not great! – user147997 Mar 30 '18 at 9:44
  • OK, I've updated the answer. If you set your desired sql_mode and then recreate the stored procedure(s) within that session, it should work. Then you don't have the change the sql_mode within the SP. – dbdemon Mar 30 '18 at 11:15
  • merry christmas. Also mysql bug 71386 – danblack Oct 13 '18 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.