CODEWITHSUNDEEP
oracle-databasedateoracle-sqldeveloper
Yalle Prado
Yalle Prado

Reputation: 43

SQL Developer displaying dates incorrectly

Using my SQL Developer Version 18.1.0.095 Build 095.1630, I put a date in the table with this value: '0001-01-01'.

But after confirm the insert, I ask a select and in the return appear '0001-01-03'.

Using the same commands in SQL Developer Version 3.2.20.09 Build MAIN-09.87, the return is like I expected, '0001-01-01'.

SQL Developer Version 18.1.0.095 Build 095.1630

DATRATTIT  TRUNC(DATR TO_TIMESTAMP(DATRATTIT)         COUNT(*)
---------- ---------- ----------------------------- ---------- 
0001-01-03 0001-01-03 0001-01-03 00:00:00,000000000     237069

SQL Developer Version 3.2.20.09 Build MAIN-09.87

DATRATTIT  TRUNC(DATRATTIT) TO_TIMESTAMP(DATRATTIT)          COUNT(*)
---------- ---------------- ------------------------------ ----------
0001-01-01 0001-01-01       0001-01-01 00:00:00,000000000      237069

Is this a configuration issue? Something to do with NLS settings? Anybody with this problem too?

Upvotes: 1

Views: 848

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Interesting, I saw something similar a few days ago but hadn't got around to probing it further. You can see the same issue with just:

alter session set nls_date_format = 'SYYYY-MM-DD';

select date '0001-01-01' from dual;

DATE'0001-0
-----------
 0001-01-03

It does seem to be a bug in the current SQL Developer though - I'm seeing it in 18.2.0.183 - and I believe the data in your table is OK - it 'just' isn't being displayed properly when you rely on implicit conversion to a string via NLS_DATE_FORMAT.

It seems to be related to Gregorian calendar and how SQL Developer is converting from internal representation. This query looks at the days around the Gregorian calendar change, and shows the client-formatted values (which are incorrect), explicit conversion to a string, and also dumps the internal representation:

with t (d) as (
  select date '1582-10-21' - level * interval '1' day from dual connect by level <= 10
)
select d, to_char(d, 'SYYYY-MM-DD'), dump(d, 1016) as dumped from t;

D           TO_CHAR(D,' DUMPED                                  
----------- ----------- ----------------------------------------
 1582-10-20  1582-10-20 Typ=13 Len=8: 2e,6,a,14,0,0,0,0         
 1582-10-19  1582-10-19 Typ=13 Len=8: 2e,6,a,13,0,0,0,0         
 1582-10-18  1582-10-18 Typ=13 Len=8: 2e,6,a,12,0,0,0,0         
 1582-10-17  1582-10-17 Typ=13 Len=8: 2e,6,a,11,0,0,0,0         
 1582-10-16  1582-10-16 Typ=13 Len=8: 2e,6,a,10,0,0,0,0         
 1582-10-15  1582-10-15 Typ=13 Len=8: 2e,6,a,f,0,0,0,0          
 1582-09-24  1582-10-04 Typ=13 Len=8: 2e,6,a,4,0,0,0,0          
 1582-09-23  1582-10-03 Typ=13 Len=8: 2e,6,a,3,0,0,0,0          
 1582-09-22  1582-10-02 Typ=13 Len=8: 2e,6,a,2,0,0,0,0          
 1582-09-21  1582-10-01 Typ=13 Len=8: 2e,6,a,1,0,0,0,0

As you can see, dates from 1582-10-15 are OK, those before are wrong; and they seem to be wrong in different ways depending how far you go back. It looks the same in script output and in a result grid.

Running the same query against the same DB but via SQL*Plus shows:

D           TO_CHAR(D,' DUMPED
----------- ----------- ----------------------------------------
 1582-10-20  1582-10-20 Typ=13 Len=8: 2e,6,a,14,0,0,0,0
 1582-10-19  1582-10-19 Typ=13 Len=8: 2e,6,a,13,0,0,0,0
 1582-10-18  1582-10-18 Typ=13 Len=8: 2e,6,a,12,0,0,0,0
 1582-10-17  1582-10-17 Typ=13 Len=8: 2e,6,a,11,0,0,0,0
 1582-10-16  1582-10-16 Typ=13 Len=8: 2e,6,a,10,0,0,0,0
 1582-10-15  1582-10-15 Typ=13 Len=8: 2e,6,a,f,0,0,0,0
 1582-10-04  1582-10-04 Typ=13 Len=8: 2e,6,a,4,0,0,0,0
 1582-10-03  1582-10-03 Typ=13 Len=8: 2e,6,a,3,0,0,0,0
 1582-10-02  1582-10-02 Typ=13 Len=8: 2e,6,a,2,0,0,0,0
 1582-10-01  1582-10-01 Typ=13 Len=8: 2e,6,a,1,0,0,0,0

and here dates before 1582-10-15 are correctly interpreted by the client too.

I don't believe there is anything you can change to fix this; my session NLS_CALENDAR is already GREGORIAN, and choosing to skip NLS settings in the preferences didn't make any difference.

The only workaround I can see at the moment is to explicitly format your date values in your queries.

But you should raise a service request with Oracle, against SQL Developer, to get it investigated properly.

Bug 28093149 : DATE IS RETURNED INCORRECTLY FOR 01/01/0001 - COMES BACK AS 03/01/0001

so you don't need to raise a service request... hopefully it'll be fixed in 18.3.

And it seems it's come up on the SD forum too, e.g. here, which references that bug number.

Upvotes: 4

Related Questions