CODEWITHSUNDEEP
oracle-database
Rick Wolff
Rick Wolff

Reputation: 777

Oracle TO_DATE is wrongly converting dates after 2020/01/07

TO_DATE in Oracle is wrongly converting the following statement:

inserir a descrição da imagem aqui

SELECT 
    to_date('20200108 000000', 'YYYYMMDD HH24MISS') D1, 
    to_date('20200107 000000', 'YYYYMMDD HH24MISS') D2
FROM dual;

Both lines are exactly the same except for the 8 instead of 7. It doesn't make sense (for me), Oracle converting Tuesday correctly and not Wednesday. There should not be any DST...

Upvotes: 1

Views: 746

Answers (2)

Rick Wolff
Rick Wolff

Reputation: 777

It was solved.

The clock on my client's server was configured to use DST and got this error because we were supposed to have DST in Brazil in 2020, but we didn't.

I believe it happened because it's an old Windows Server and might have not updated the DST tables.

Updating the clock on the server solved the issue.

The error itself I believe was generated by the application's driver, and not by Oracle.

Upvotes: 0

ekochergin
ekochergin

Reputation: 4129

It is the tool you're using, the SQL Developer. It overrides the DB NLS settings with their own.

Go to Tools -> Preferences, then Database -> NLS and check the settings

The tool's NLS settings

Upvotes: 1

Related Questions