Date Format issue STR_TO_DATE('18:11:52', '%H:%i:%s') Returns NULL

Question

I am trying to change a string to time format but it seems some issue at my database configuration. because when I am trying following query it gives me null in return

mysql> select STR_TO_DATE('18:11:52', '%H:%i:%s');
+-------------------------------------+
| STR_TO_DATE('18:11:52', '%H:%i:%s') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '18:11:52' for function str_to_date |
+---------+------+---------------------------------------------------------------+

But when I try same query in another Mysql Server it goes fine. There must be some configuration issue. I appreciate if anyone can help. Thanks in advance.

Answer 1

This means you have the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode enabled, so MySQL will not allow you to convert a time string with an implicit date of 0000-00-00 to a datetime.

Since your input value is actually a time string (not a datetime) it might make more sense for you to use the CONVERT() function, like this:

SELECT CONVERT('18:11:52', TIME);