PL/SQL (MOSC)

MOSC Banner

to_date on a date gives wrong answer

in PL/SQL (MOSC) 12 commentsAnswered ✓

Hi, before people start saying why do you do a to_date on a date, yes we should not but we did.

what happens (both in oracle 12.1 and 19.16):

given a nls_date_format like dd-mm-yy and one executes a to_date on a date using dd-mm-yyyy as the format to use, we loose the century:

example code:

ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yy';

select to_char(to_date(sysdate,'dd-mon-yyyy'),'dd-mm-yyyy') wrong from dual;

result:

WRONG

----------

25-04-0023 >>>>>>>>>>>>>>>>>>>>>>>>>>>>

It gave the correct result in January.

Can any body answer with a database version where it gives 2023 instead of 0023

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center