I have the following statement that in other installations of MySQL appeared to work fine and return a TIME, while now returns a NULL (which, according to documentation, implies a conversion failure):
mysql> select STR_TO_DATE('17:54:23', '%H:%i:%s') AS test;
+------+
| test |
+------+
| NULL |
+------+
1 row in set, 1 warning (0,00 sec)
mysqls> show warnings;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '17:54:23' for function str_to_date |
+---------+------+---------------------------------------------------------------+
1 row in set (0,00 sec)
And yet according to docs " It takes a string str and a format string format. 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". As said, used to work in previous versions, and I have people with other installations of 5.7 telling me that it works for them.
Info on the local MySQL version:
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name | Value |
+-------------------------+-------------------------+
| innodb_version | 5.7.20 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.20-0ubuntu0.16.04.1 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+-------------------------+
The installation is "out of the box", Linux Mint, with no tweaking. Anyone has a clue as to why it does not work?
Doh, it just required to read the docs a bit further it seems:
"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:"