Re: is there a bug in decode function with respect to date

From: Johnnie Leung <jsleung_at_telecom-digest.zzn.com>
Date: 2000/03/15
Message-ID: <g9%z4.1213$uX5.82666@news.uswest.net>#1/1

>
> select
>

to_char(to_date(decode(col1,'01-feb-00',null,col1,col1),'dd-mon-yy'),'MM-DD-
> YYYY') from test_ajay;
>
> The out put looks like
>
> 02-01-2082
>
> 1 row selected.
>

Not a bug; this is a 'feature'. In Oracle, if you use to_date() with the short year format 'YY', it's always assumed to be in the current century ('RR' has some fuzzy logic that guesses the intended century). Your use of decode() here implicitly converts (casts) a date into a string that has the short year format, which explains the lossiness.

This is probably closer to what you really want:

select to_char(to_date(decode(col1,to_date('01-feb-00', 'DD-mon-YY'),null,col1,col1),'dd-mon-yy'),'MM-DD-YYYY') from test_ajay;

This keeps the value in the date format throughout.

JL Received on Wed Mar 15 2000 - 00:00:00 CST