Bug #110093 STR_TO_DATE %p does not work
Submitted: 16 Feb 2023 6:47 Modified: 17 Feb 2023 10:17
Reporter: Derek Main Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[16 Feb 2023 6:47] Derek Main
Description:
I'm am migrating a database from Oracle to MySQL. When I try to convert the dates using STR_TO_DATE function, the %p to convert AM / PM dates does not work.

select STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f');
returns : 
2020-03-26 09:01:00.000000

select STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f %p');
returns:
NULL

How to repeat:
Run the same commands.

Suggested fix:
Fix bug or suggest alternative please.
[16 Feb 2023 7:25] Derek Main
This works :
SELECT STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %h.%i.%s.%f000 %p');
Returns:
2020-03-26 21:01:00.000000
[16 Feb 2023 14:18] MySQL Verification Team
Hi Mr. Main,

Thank you for your bug report.

There are several functions that are supposed to support %p, but none is supporting it .......

Seems that it was never implemented.

Thank you for your report.

This report is now a verified feature request.
[16 Feb 2023 14:19] MySQL Verification Team
.
[17 Feb 2023 10:13] Norvald Ryeng
Posted by developer:
 
Hi Derek,

I'm closing this as not a bug. Let's take this query by query (I'm using 8.0, but 5.7 should be the same):

mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f');
+------------------------------------------------------------------------+
| STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f') |
+------------------------------------------------------------------------+
| 2020-03-26 09:01:00.000000                                             |
+------------------------------------------------------------------------+

As the manual states, "Extra characters at the end of str are ignored" (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date). So parsing ends after microseconds, and the suffix "000 PM" is ignored. This is correct.

Next query:

mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f %p');
+---------------------------------------------------------------------------+
| STR_TO_DATE('26-MAR-20 09.01.00.000000000 PM', '%d-%b-%y %H.%i.%s.%f %p') |
+---------------------------------------------------------------------------+
| NULL                                                                      |
+---------------------------------------------------------------------------+

In this case, the format is not matched. There format matches up to %f, but the remaining string "000 PM" does not match the rest of the pattern (" %p"). Even if we reduce the fractional part to microseconds, we still get NULL:

mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %H.%i.%s.%f %p');
+------------------------------------------------------------------------+
| STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %H.%i.%s.%f %p') |
+------------------------------------------------------------------------+
| NULL                                                                   |
+------------------------------------------------------------------------+

This is because the pattern contains two conflicting hour specifications. %H means hours in 24 hour format, while %p says if it is AM or PM. If we change the pattern to use a 12 hour format (%h), we get the result you expect:

mysql> select STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %h.%i.%s.%f %p');
+------------------------------------------------------------------------+
| STR_TO_DATE('26-MAR-20 09.01.00.000000 PM', '%d-%b-%y %h.%i.%s.%f %p') |
+------------------------------------------------------------------------+
| 2020-03-26 21:01:00.000000                                             |
+------------------------------------------------------------------------+

Don't combine 24-hour hours (%H or %k) with AM/PM (%p). Instead, if you need AM/PM matching, use 12-hour hours (%h, %I or %l), or use %r to match the entire string of hours, minutes, seconds and AM/PM.

Best regards,

Norvald
[17 Feb 2023 10:17] Derek Main
Thanks for the explanation Norvald. I had missed the limit of 6 for microseconds in the documentation. That was the root of my problem.
[17 Feb 2023 13:19] MySQL Verification Team
Thank you, Norvald ........