CODEWITHSUNDEEP
oracle-sqldeveloper
Seymour
Seymour

Reputation: 3264

sql developer issue in visualizing date

since few weeks I started working on SQL developer.

I noticed an important issue that I do not know how to solve. This is related to the way my SQL developer visualizes the date.

First of all, here are my settings: enter image description here

and here is my problem:

select to_date('01-01-0001', 'DD-MM-YYYY') from dual;

Instead of correctly visualizin 01-01-2001, it visualizes 03-JAN-0001

enter image description here

select select to_date('01-01-2001', 'DD-MM-YYYY') from dual;

It is interesting to observe that the issue of the day disappear when we change year.

enter image description here

Upvotes: 0

Views: 644

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22427

So this -

enter image description here

  • defines for your session how to display dates and timestamps.

Example:

enter image description here

If you were querying a date w/o supplying a date format, then it adopts said settings.

But if you query a string, and ask Oracle to treat it as a date

select to_date('1-1-1990', 'DD-MM-YYYY') from dual;

The DD-MM-YYYY tells Oracle how to interpret your string as a date.

And then the output is shown using the format defined for NLS

enter image description here

Now, for your scenario, the year '1' - there is a bug in SQL Developer 18.1 that is causing the date to come back as 3 vs 1.

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

Upvotes: 3

Related Questions