Pitfall: ORA-1843 – NOT A VALID MONTH in Oracle 19.4 – 19.8

Today, I’d like to write about a tiny little issue – which can cause some real trouble in Oracle 19c. This blog post is about the Pitfall: ORA-1843 – NOT A VALID MONTH in Oracle 19.4 – 19.8.

Photo by Daniel J. Schwarz on Unsplash

The Testcase

My testcase is super-simple – and you can reproduce it even without any object in 2 seconds (one for “copy“, the other for “paste“). Simply execute this query:

select to_date('20191120','RRMMDD') from dual;

Or this one where I replace “RR” with the more common “YY”:

select to_date('20191120','YYMMDD') from dual;

Result in Oracle 11.2, 12.1, 12.2.0.1 and 18c

I do this test in 12.2.0.1 – and the expected result is:

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 20 10:01:18 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select to_date('20191120','RRMMDD') from dual;

TO_DATE('20191120'
------------------
20-NOV-19

SQL> select to_date('20191120','YYMMDD') from dual;

TO_DATE('20191120'
------------------
20-NOV-19

Everything ok.

Result in Oracle 19.4, 19.5, 19.6, 19.7 and 19.8

And now I repeat the same test in Oracle 19c, in my case in 19.8.0:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 20 10:37:41 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select to_date('20191120','RRMMDD') from dual;
select to_date('20191120','RRMMDD') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('20191120','YYMMDD') from dual;
select to_date('20191120','YYMMDD') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Ups.

Not good. And not expected.

Background Information

When you search in MOS with “ORA-1843 not a valid month to_date“, the first result is this note:

• MOS Note: 2656012.1 – ORA-1843 Occurs When Executing TO_DATE(‘20200101′,’RRMMDD’) or TO_DATE(‘20200101′,’YYMMDD’)

And there you will find the bug number as well – but the bug is non-public, hence, no link added:

• Bug 30565805 – ORA-01843: NOT A VALID MONTH ERROR WHEN EXECUTING TO_DATE(‘20191120′,’RRMMDD’)

The issue got introduced as a regression of the fix for bug 28509578.

Solution

What I dislike quite a bit in recent MOS notes is this sentence as proposed solution:

Please apply Patch 30565805.
or
Upgrade to Oracle Database 20c.

At first, Oracle 20c is not available on-prem. And furthermore, the release notes for Oracle 20c tell you in the header on page 1:

Oracle Database 20c is available only for preview. It is not available for production use. Upgrades to or from Oracle Database 20c are not supported.

So you can’t upgrade to it, even if it would be available.

But the important message for you is:

• There are one-off fixes available for every 19c RU, and even for some RURs
• The fix will be included in the 19.9.0 October Release Update

More Information and Links

• MOS Note: 2656012.1 – ORA-1843 Occurs When Executing TO_DATE(‘20200101′,’RRMMDD’) or TO_DATE(‘20200101′,’YYMMDD’)
• One-Off Patch 30565805
• Release Notes Oracle 20c

–Mike