Details
-
Bug
-
Status: Open (View Workflow)
-
Trivial
-
Resolution: Unresolved
-
N/A
-
None
Description
Oracle forbids using identical or similar format elements on the same format string more than once. I don't know why they do it, but the behavior seems at least consistent. I didn't try all elements, but for those which I tried, they always cause an error, either ORA-01810 for identical elements, or element-specific for semantically similar:
|
Oracle 21c |
SELECT TO_DATE('2001 2001', 'YYYY YYYY') FROM DUAL; |
ORA-01810: format code appears twice
|
|
|
SELECT TO_DATE('20 20', 'DD DD') FROM DUAL; |
ORA-01810: format code appears twice
|
|
|
SELECT TO_DATE('February February', 'MONTH MONTH') FROM DUAL; |
ORA-01810: format code appears twice
|
|
Oracle 21c |
SELECT TO_DATE('2001 001', 'YYYY YYY') FROM DUAL; |
ORA-01812: year may only be specified once |
|
|
SELECT TO_DATE('Feb February', 'MON MONTH') FROM DUAL; |
ORA-01816: month may only be specified once |
|
|
SELECT TO_DATE('Monday Mon', 'DAY DY') FROM DUAL; |
ORA-01817: day of week may only be specified once |
In MariaDB, the behavior seems sporadic. It would be understandable if it either forbade everything as Oracle does, or allowed everything as common sense suggests; but it allows some while forbids the most. For example, out of the above, the first 5 cause ER_STD_INVALID_ARGUMENT, while the last one works:
|
bb-12.3-MDEV-19683-to_date d5fb3dccd72d816c7181a069b91eedb3c76b2715 |
MariaDB [test]> SELECT TO_DATE('2001 2001', 'YYYY YYYY') FROM DUAL; |
ERROR 3047 (HY000): Invalid argument error: date format not recognized at YYYY in function to_date. |
MariaDB [test]> SELECT TO_DATE('20 20', 'DD DD') FROM DUAL; |
ERROR 3047 (HY000): Invalid argument error: date format not recognized at DD in function to_date. |
MariaDB [test]> SELECT TO_DATE('February February', 'MONTH MONTH') FROM DUAL; |
ERROR 3047 (HY000): Invalid argument error: date format not recognized at MONTH in function to_date. |
MariaDB [test]> SELECT TO_DATE('2001 001', 'YYYY YYY') FROM DUAL; |
ERROR 3047 (HY000): Invalid argument error: date format not recognized at YYY in function to_date. |
MariaDB [test]> SELECT TO_DATE('Feb February', 'MON MONTH') FROM DUAL; |
ERROR 3047 (HY000): Invalid argument error: date format not recognized at MONTH in function to_date. |
MariaDB [test]> SELECT TO_DATE('Monday Mon', 'DAY DY') FROM DUAL; |
+---------------------------------+ |
| TO_DATE('Monday Mon', 'DAY DY') | |
+---------------------------------+ |
| 2026-02-09 00:00:00 |
|
+---------------------------------+ |
1 row in set (0.001 sec) |
(note that on reproducing on a day other than Monday in February, either the queries or the timestamp needs to be modified).
Attachments
Issue Links
- is caused by
-
MDEV-19683 Add support for Oracle TO_DATE()
-
- Approved
-