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:
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
Hi Mike,
Well, I’m sure I’m not the only one who will say this is not a bug. It’s just plain wrong to expect Oracle to have understood that in the first place.
In 20 years of developing PL/SQL (before I returned to dba roots), I would never have thought this could be correct.
Just do it properly and it works fine:
Either “select to_date(‘20191120′,’YYYYMMDD’) from dual;”
or “select to_date(‘191120′,’RRMMDD’) from dual;”
Regards,
Stuart
Hi Stuart,
logically I fully agree – and I was puzzled at first stage as well as I would have expected it to fail.
But unfortunately this worked for ages – and it seems that applications are based on this “mis”behavior.
Cheers,
Mike
Hi Mike,
Thanks for update ,
fyi : when I tried same sql with ‘YYYY’ or ‘RRRR’ its working fine.
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′,’RRRRMMDD’) from dual;
TO_DATE(‘20191120’,
——————-
2019-11-20 00:00:00
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
SQL> select to_date(‘20191120′,’YYYYMMDD’) from dual ;
TO_DATE(‘20191120’,
——————-
2019-11-20 00:00:00
SQL>
In 19c, both SQL work fine:
select to_date(‘20191120′,’YYYYMMDD’) from dual;
select to_date(‘20191120′,’RRRRMMDD’) from dual;
for 4-digit year, need 4-letter ‘YYYY’.
Hi Mike,
thanks for the notification! That is indeed a nasty little bugger.
I have just tested on a freshly migrated database 19.8 on windows – the error occurs here, too.
Why am I not surprised that there is (again) no One-Off Patch for Windows…? At least I only found Linux, Solaris and AIX in the patch list.
Thanks and best regards,
Susanne
Hi Susanne,
as far as I’m aware, on Windows we don’t produce one-off patches 🙁
But you may need to double check with Support please.
Cheers,
Mike
It seems ok for 19.8.0.
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Wed Aug 26 21:09: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’,’YYYYMMDD’) from dual;
TO_DATE(‘
———
20-NOV-19
Hi Mike,
As soon as I read your “The Testcase” section, I asked myself, shouldn’t this fail even in the prior versions of Oracle? Why supply less format than the input. I would expect four RRRR as part of RRRRMMDD format because you are supplying 2019 as year, not just 19.
Just for the records, I ran your code on Oracle 19.3.0.0.0 on Windows and it failed with “ORA-01843: not a valid month” as expected. But, following works, again, as expected
SQL> SELECT TO_DATE(‘20200826′,’RRRRMMDD’) FROM DUAL;
Thanks.
Parag
Logically I fully agree – but it worked for ages as I understood – so this is a behavior change which may break applications.
But again, logically I’m with you 🙂
Cheers,
Mike
But isn’t there a mismatch between the date supplied and the mask?
‘20191120’,’YYMMDD’
A full year is being supplied (2019) yet the mask only deals with YY.
I almost feel like this should have never worked in previous versions.
As commented, logically I agree – but this has worked for ages as I understood. And application have been settled on it.
Cheers,
Mike
Hi, did you install 19.8c on RHEL 8? I had run an orachk report and it is empty, could be a bug?
Regards
Please see the blog posts and especially the comments about OL8:
https://mikedietrichde.com/2020/05/14/of-course-oracle-clusterware-is-certified-on-ol8-rhel8-as-well/
https://mikedietrichde.com/2020/05/11/oracle-database-19c-is-certified-on-ol8-and-rhel8/
Cheers,
Mike
For Linux, there is a one-off patch 30565805. For Windows, we have to wait for 19.9 – correct?
Please check
Bug 31859254 – BACKPORT OF 30565805 ON WINDOWS DATABASE BP 19.8.0.0.200714
with Oracle Support – not done yet but queued.
Cheers,
Mike