| Bug #80064 | SELECT STR_TO_DATE('09:30:17','%h:%i:%s') return NULL value | ||
|---|---|---|---|
| Submitted: | 20 Jan 2016 8:50 | Modified: | 26 May 2016 15:01 |
| Reporter: | Sergey Norov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.7.9,5.7.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression, STR_TO_DATE | ||
[20 Jan 2016 8:59]
Sergey Norov
Next sql run fine on mysql 5.6.27, 5.7.9:
mysql> SELECT STR_TO_DATE('02.09.2015 14:00:00', '%d.%m.%Y %H:%i:%s');
+---------------------------------------------------------+
| STR_TO_DATE('02.09.2015 14:00:00', '%d.%m.%Y %H:%i:%s') |
+---------------------------------------------------------+
| 2015-09-02 14:00:00 |
+---------------------------------------------------------+
1 row in set (0,00 sec)
[20 Jan 2016 9:47]
MySQL Verification Team
Hello Sergey, Thank you for the report and test case. Thanks, Umesh
[26 May 2016 15:01]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly. Added to http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date: 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: mysql> SET sql_mode = ''; mysql> SELECT STR_TO_DATE('15:35:00', '%H:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%H:%i:%s') | +-------------------------------------+ | 15:35:00 | +-------------------------------------+ mysql> SET sql_mode = 'NO_ZERO_IN_DATE'; mysql> SELECT STR_TO_DATE('15:35:00', '%h:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%h:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1411 Message: Incorrect datetime value: '15:35:00' for function str_to_date
Description: System: mysql server 5.7.9 ubuntu 14.04 (AND mysql server 5.7.10 OSX): SELECT STR_TO_DATE('15:35:00', '%H:%i:%s'); RETURN: +-------------------------------------+ | STR_TO_DATE('15:35:00', '%H:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set, 1 warning (0,02 sec) and run next sql: mysql> SELECT STR_TO_DATE('15:35:00', '%h:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%h:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set, 2 warnings (0,00 sec) In manual on www dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s'); -> '09:30:17' mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s'); -> NULL mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s'); -> '09:30:17' ON Server version: 5.6.27 MySQL Community Server (GPL) centos 6.x mysql> SELECT STR_TO_DATE('15:35:00', '%H:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%H:%i:%s') | +-------------------------------------+ | 15:35:00 | +-------------------------------------+ 1 row in set (0,00 sec) mysql> SELECT STR_TO_DATE('15:35:00', '%h:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%h:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set, 1 warning (0,00 sec) How to repeat: mysql> SELECT STR_TO_DATE('15:35:00', '%h:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%h:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set, 2 warnings (0,01 sec) mysql> SELECT STR_TO_DATE('15:35:00', '%H:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%H:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set, 1 warning (0,00 sec) On mysql Server version: 5.6.27 version: SELECT STR_TO_DATE('15:35:00', '%H:%i:%s'); mysql> SELECT STR_TO_DATE('15:35:00', '%H:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%H:%i:%s') | +-------------------------------------+ | 15:35:00 | +-------------------------------------+ 1 row in set (0,00 sec) mysql> SELECT STR_TO_DATE('15:35:00', '%h:%i:%s'); +-------------------------------------+ | STR_TO_DATE('15:35:00', '%h:%i:%s') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set, 1 warning (0,00 sec)