MySQL Bugs: #4756: STR_TO_DATE() returning bad results with AM/PM

Bug #4756	STR_TO_DATE() returning bad results with AM/PM
Submitted:	26 Jul 2004 19:09	Modified:	10 Aug 2004 2:56
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4	OS:
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
STR_TO_DATE() is returning bad results for format strings involving AM/PM (whether from %r or %p).

How to repeat:
SELECT STR_TO_DATE(DATE_FORMAT('2004.07.26 12:00:00 PM', '%m/%e/%Y %r'), '%m/%e/%Y %r') AS a;
SELECT STR_TO_DATE(DATE_FORMAT('2004.07.26 12:00:00 PM', '%m/%e/%Y %h:%i:%S %p'), '%m/%e/%Y %h:%i:%S %p') AS a;

Suggested fix:
n/a

Since DATE_FORMAT() function accepts datetime value in one of standard MySQL datetime formats, PM part of parameter is simply ignored (and warning about data truncation is issued). So cleaned up test case should look like:

SELECT STR_TO_DATE(DATE_FORMAT('2004.07.26 12:00:00', '%m/%e/%Y %h:%i:%S %p'), '%m/%e/%Y %h:%i:%S %p') AS a;
SELECT STR_TO_DATE(DATE_FORMAT('2004.07.26 12:00:00', '%m/%e/%Y %r'), '%m/%e/%Y %r') AS a;

Which demonstrates that there is a problem only with %r flag.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.1909.27.1 2004/08/06 10:01:29 dlenev@brandersnatch.localdomain
  Fix for bug #4756 "STR_TO_DATE() returning bad results with AM/PM".
  Added support of converion specifiers mentioned in manual but missing in code.