SQL Developer and SQLcl Displaying the Correct Date Although the Bytes Stored in the Block Are Invalid (Doc ID 2996249.1)

Last updated on JANUARY 05, 2024

Applies to:

Symptoms

You have some invalid DATE rows in a table, and after reviewing Document 331831.1 and Document 69028.1, you understand how the rows got there (Cobol process as part of Peoplesoft processing).

When query the invalid dates using SQL*Plus, the dates show invalid dates, as expected. However, when query the same invalid dates using SQL Developer or SQLcl, the dates show valid ones.

For Example:
sqlplus displays it as "00/09/2022", but SQL Developer displays the value as "31/08/2022". In this case, sqlplus has no nls_date_format session settings, as they are inherited from database NLS settings.

Dumping the block and value the 7 byte date is indeed invalid, but how does the SQL Developer show the "correct" date and not an invalid one?

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.