| Bug #79849 | STR_TO_DATE function not working for numeric month (0...12) | ||
|---|---|---|---|
| Submitted: | 5 Jan 2016 13:08 | Modified: | 8 Feb 2016 10:06 |
| Reporter: | Saharsh Shah | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.6.23, 5.6.28, 5.7.10 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date-format, str-to-date | ||
[5 Jan 2016 15:33]
MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
[6 Jan 2016 9:11]
Saharsh Shah
I am not asking for any help, I found the bug in STR_TO_DATE() function to convert string into date. %c is used for Numeric month but when I use that in my query it is not working. Check the question in detail and provide the patch update to fix this issue...
[7 Jan 2016 7:39]
MySQL Verification Team
Thank you for the feedback.
It seems STR_TO_DATE for specifier %c expect month in 00..12 just like %m.
mysql> SELECT STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%d%c%Y' ), '%d%c%Y');
+---------------------------------------------------------------+
| STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%d%c%Y' ), '%d%c%Y') |
+---------------------------------------------------------------+
| 0016-12-07 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_FORMAT(CURRENT_DATE(), '%d%c%Y' );
+----------------------------------------+
| DATE_FORMAT(CURRENT_DATE(), '%d%c%Y' ) |
+----------------------------------------+
| 0712016 |
+----------------------------------------+
1 row in set (0.00 sec)
-- month in 0..12 - wrong
mysql> SELECT STR_TO_DATE('0712016','%d%c%Y');
+---------------------------------+
| STR_TO_DATE('0712016','%d%c%Y') |
+---------------------------------+
| 0016-12-07 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('07012016','%d%c%Y'); -- with month in 00.12 format
+----------------------------------+
| STR_TO_DATE('07012016','%d%c%Y') |
+----------------------------------+
| 2016-01-07 |
+----------------------------------+
1 row in set (0.00 sec)
-- with month in 00.12 format, c% works
mysql> SELECT STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%d%m%Y' ), '%d%m%Y');
+---------------------------------------------------------------+
| STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%d%m%Y' ), '%d%m%Y') |
+---------------------------------------------------------------+
| 2016-01-07 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
[8 Feb 2016 10:06]
Saharsh Shah
As per 5.6 Reference Manual "%c" is used for "Month, numeric (0..12)". Link: https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format Please check and resolve the problem...
[8 Feb 2016 10:09]
Saharsh Shah
MySQL DateFormat function
Attachment: MySQL DateFormat.png (image/png, text), 106.71 KiB.
Description: I am trying to convert 0512016 (DDMYYYY) format to MySQL's Date format(YYYY-MM-DD). But I am not able to do using STR_TO_DATE(). Check the below query: SELECT STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%d%c%Y' ), '%d%c%Y'); ***OR*** SELECT STR_TO_DATE('0512016', '%d%c%Y'); The above queries must return "2015-01-05", but the query returns "0015-12-05". Please check the issue and resolve it. How to repeat: 1. SELECT STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%d%c%Y' ), '%d%c%Y'); 2. SELECT STR_TO_DATE('0512016', '%d%c%Y'); Suggested fix: Change the code for "%c" format to support single digit / double digit month number.