Incorrect value for str_to_date 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&gt; 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
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&gt; 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&gt; 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)]&gt; 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&gt; SET sql_mode='';
mysql&gt; 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.