My Oracle Support Banner

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:

Oracle SQL Developer - Version 21.4.3 and later
Oracle SQLcl - Version 21.2 and later
Information in this document applies to any platform.


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?


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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