Bug #4756 STR_TO_DATE() returning bad results with AM/PM
Submitted: 26 Jul 2004 21:09 Modified: 10 Aug 2004 4:56
Reporter: Dean Ellis
Status: Closed
Category:Server Severity:S2 (Serious)
Version:4.1.4 OS:
Assigned to: Dmitri Lenev Target Version:

[26 Jul 2004 21:09] Dean Ellis 
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
[2 Aug 2004 23:56] Dmitri Lenev 
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.
[10 Aug 2004 4:56] Dmitri Lenev 
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.