CODEWITHSUNDEEP
sqloracle-database
BrilliantContract
BrilliantContract

Reputation: 305

Oracle to_date() return incorrect date with +2 days

Oracle to_date() function return incorrect date with +2 days.

Please take a look on screenshot

enter image description here

Why is it happened?

Upvotes: 0

Views: 1736

Answers (2)

Techienik
Techienik

Reputation: 71

The problem is you are not matching the format you are providing.

Year is provided as 18 and TO_DATE expects 2018.

Below should work fine:

select to_date('11-MAY-18', 'DD-MON-YY') from dual;

OR

select to_date('11-MAY-2018', 'DD-MON-YYYY') from dual;

Upvotes: 1

MT0
MT0

Reputation: 167832

I can't replicate your result, but I do get the wrong date for 1-MAY-18 on SQL Fiddle even if a date literal is used (but not if I use my Oracle 11g instance or Oracle 18c on Oracle's Live SQL website):

SQL Fiddle

Query 1:

SELECT TO_DATE( '1-MAY-18', 'DD-MON-YYYY' ),
       DATE '0018-05-01',
       TO_DATE( '1-MAY-18', 'DD-MON-RR' )
FROM   DUAL

Results:

| TO_DATE('1-MAY-18','DD-MON-YYYY') |     DATE'0018-05-01' | TO_DATE('1-MAY-18','DD-MON-RR') |
|-----------------------------------|----------------------|---------------------------------|
|              0018-04-29T00:00:00Z | 0018-04-29T00:00:00Z |            2018-05-01T00:00:00Z |

You will note that the YYYY format model and the numeric value 18 gives the year 18 AD and not 2018 AD. To fix this you need to either give the entire 4-digit year or use the RR format model.

Upvotes: 2

Related Questions