Oracle TO_DATE is converting wrong dates from day 07/01/2020

Asked

Viewed 206 times

3

The TO_DATE function of the Oracle database is doing the following conversion wrong:

inserir a descrição da imagem aqui

The two lines are exactly the same. It makes no sense (for me), the Oracle convert right the date of Tuesday and miss the Wednesday. No daylight savings or anything...

Consultation:

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

  • Just to make sure that there really is no daylight saving or anything, what time is he showing in the result? If you change the time to 120000, what happens?

  • I DID IT ON MY SELECT ENVIRONMENT to_date('20200108 000000', 'YYYYMMDD HH24MISS') D1, to_date('20200107 000000', 'YYYYMMDD HH24MISS') D2 FROM dual; D1 --------------- 08/01/20 07/01/20

  • No sql fiddle also returned the correct result: http://sqlfiddle.com/#! 4/a931c6/1

  • It must be something related to my environment. Thank you guys. I can’t change the time to 1200000. @Victorstafusa In the first column, it shows 00:00:00, in the second show 23:00:00

  • Check which Timezone is set for your installation.

  • How do I do that?

Show 1 more comment

1 answer

0


My client’s server had a clock set for daylight saving time and was left with this error because in 2020 we were supposed to have daylight saving time in Brazil. I imagine it happened to be an old Windows Server and not have updated the tables for daylight saving time.

Setting the clock solved everything.

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

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.