Description:
I have a simple query that is failing on str_to_date. I've tried a number of format combinations to no avail.
I have also submitted an inquiry to the developer's forum, someone suggested to submit this as a bug.
Here is the original problem query.
mysql> select severity, createdate,
-> date_format(createdate,'%Y-%m') mth_yr, closure_time,
-> date_format(date_sub(now(),interval 7 day),'%b %d, %Y / %l:%i %p') ct ,
-> -- date_format(str_to_date(date_format(date_sub(now(),interval 4 hour),'%b %d, %Y / %l:%i %p'),'%b %d, %Y / %l:%i %p'),'%Y-%m') ct0
-> -- date_format(closure_time,'%b %d, %Y / %l:%i %p') ct0
-> str_to_date(closure_time,'%b %d, %Y / %l:%i %p') ct0
-> -- str_to_date(date_format(closure_time,'%b %d, %Y / %l:%i %p'),'%Y-%m') ct0
-> from prob_mgmt where createdate > date_sub(now(), interval 9 day)
-> and closure_time = 'Aug 29, 2006 / 2:10 PM'
-> order by createdate;
+----------+---------------------+---------+------------------------+-------------------------+------+
| severity | createdate | mth_yr | closure_time | ct | ct0 |
+----------+---------------------+---------+------------------------+-------------------------+------+
| Sev 4 | 2006-08-28 16:28:31 | 2006-08 | Aug 29, 2006 / 2:10 PM | Aug 25, 2006 / 12:19 PM | NULL |
| Sev 4 | 2006-08-28 16:29:50 | 2006-08 | Aug 29, 2006 / 2:10 PM | Aug 25, 2006 / 12:19 PM | NULL |
+----------+---------------------+---------+------------------------+-------------------------+------+
2 rows in set, 2 warnings (0.09 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------+
| Error | 1411 | Incorrect datetime value: 'Aug 29, 2006 / 2:10 PM' for function str_to_time |
| Error | 1411 | Incorrect datetime value: 'Aug 29, 2006 / 2:10 PM' for function str_to_time |
+-------+------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Futher information about my environment.
mysql Ver 14.12 Distrib 5.0.24, for pc-linux-gnu (i686) using readline 5.0
Connection id: 2
Current database: intranet
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.24-standard
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 20 hours 54 min 2 sec
How to repeat:
Test case that uses just the two functions to highlight the problem.
mysql> select str_to_date('Sep 01, 2006 / 12:24 PM','%b %d, %Y / %l:%i %p'), date_format(now(),'%b %d, %Y / %l:%i %p');
+---------------------------------------------------------------+-------------------------------------------+
| str_to_date('Sep 01, 2006 / 12:24 PM','%b %d, %Y / %l:%i %p') | date_format(now(),'%b %d, %Y / %l:%i %p') |
+---------------------------------------------------------------+-------------------------------------------+
| NULL | Sep 01, 2006 / 12:24 PM |
+---------------------------------------------------------------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------+
| Error | 1411 | Incorrect datetime value: 'Sep 01, 2006 / 12:24 PM' for function str_to_time |
+-------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Description: I have a simple query that is failing on str_to_date. I've tried a number of format combinations to no avail. I have also submitted an inquiry to the developer's forum, someone suggested to submit this as a bug. Here is the original problem query. mysql> select severity, createdate, -> date_format(createdate,'%Y-%m') mth_yr, closure_time, -> date_format(date_sub(now(),interval 7 day),'%b %d, %Y / %l:%i %p') ct , -> -- date_format(str_to_date(date_format(date_sub(now(),interval 4 hour),'%b %d, %Y / %l:%i %p'),'%b %d, %Y / %l:%i %p'),'%Y-%m') ct0 -> -- date_format(closure_time,'%b %d, %Y / %l:%i %p') ct0 -> str_to_date(closure_time,'%b %d, %Y / %l:%i %p') ct0 -> -- str_to_date(date_format(closure_time,'%b %d, %Y / %l:%i %p'),'%Y-%m') ct0 -> from prob_mgmt where createdate > date_sub(now(), interval 9 day) -> and closure_time = 'Aug 29, 2006 / 2:10 PM' -> order by createdate; +----------+---------------------+---------+------------------------+-------------------------+------+ | severity | createdate | mth_yr | closure_time | ct | ct0 | +----------+---------------------+---------+------------------------+-------------------------+------+ | Sev 4 | 2006-08-28 16:28:31 | 2006-08 | Aug 29, 2006 / 2:10 PM | Aug 25, 2006 / 12:19 PM | NULL | | Sev 4 | 2006-08-28 16:29:50 | 2006-08 | Aug 29, 2006 / 2:10 PM | Aug 25, 2006 / 12:19 PM | NULL | +----------+---------------------+---------+------------------------+-------------------------+------+ 2 rows in set, 2 warnings (0.09 sec) mysql> show warnings; +-------+------+-----------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------+ | Error | 1411 | Incorrect datetime value: 'Aug 29, 2006 / 2:10 PM' for function str_to_time | | Error | 1411 | Incorrect datetime value: 'Aug 29, 2006 / 2:10 PM' for function str_to_time | +-------+------+-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Futher information about my environment. mysql Ver 14.12 Distrib 5.0.24, for pc-linux-gnu (i686) using readline 5.0 Connection id: 2 Current database: intranet Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.24-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 20 hours 54 min 2 sec How to repeat: Test case that uses just the two functions to highlight the problem. mysql> select str_to_date('Sep 01, 2006 / 12:24 PM','%b %d, %Y / %l:%i %p'), date_format(now(),'%b %d, %Y / %l:%i %p'); +---------------------------------------------------------------+-------------------------------------------+ | str_to_date('Sep 01, 2006 / 12:24 PM','%b %d, %Y / %l:%i %p') | date_format(now(),'%b %d, %Y / %l:%i %p') | +---------------------------------------------------------------+-------------------------------------------+ | NULL | Sep 01, 2006 / 12:24 PM | +---------------------------------------------------------------+-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------+ | Error | 1411 | Incorrect datetime value: 'Sep 01, 2006 / 12:24 PM' for function str_to_time | +-------+------+------------------------------------------------------------------------------+ 1 row in set (0.00 sec)