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)

Thank you for the report.

Verified on Linux using last BK sources as described with one addition:
space+not-fromat-symbol gives the error:

mysql> SELECT STR_TO_DATE('04 /30/2004', '%m /%d/%Y');
+-----------------------------------------+
| STR_TO_DATE('04 /30/2004', '%m /%d/%Y') |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13104

ChangeSet@1.2543, 2006-10-05 15:29:00+05:00, ramil@mysql.com +3 -0
  Fix for bug #22029: str_to_date returning NULL, while date_format works using identical
format.
  
  The problem appears when we have a space followed by a non-format symbol.
  
  Fix: properly skip spaces.

Looks fine, Ramil.

Noted in 4.1.23, 5.0.32, 5.1.15 changelogs.

STR_TO_DATE() returned NULL if the format string contained a space 
following a non-format character.