Re: decode/to_date in an insert

euroman_at_my-deja.com wrote:
>
> In an INSERT statement, using to_date (decode ()) works fine but decode
> (to_date ()) gets the current century, as follows:
>
> create table z (x date);
> insert into z values (to_date ('19990304', 'yyyymmdd'));
> select to_char (x, 'mm/dd/yyyy') from z ;
> /*
> returns
>
> 03/04/1999
>
> as expected
> */
> insert into z values (decode ('a', 'b', null, to_date
> ('19450506','yyyymmdd')));
> select to_char (x, 'mm/dd/yyyy') from z ;
> /*
> returns
> TO_CHAR(X,
> ----------
> 03/04/1999
> 05/06/2045 -- NOT EXPECTED
> However,
> */
> insert into z values (to_date (decode ('a', 'b',
> null, '19340302'), 'yyyymmdd'));
> select to_char (x, 'mm/dd/yyyy') from z ;
> /*
> returns
> TO_CHAR(X,
> ----------
> 03/04/1999
> 05/06/2045
> 03/02/1934
> */
>
> Any explanation is appreciated. Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Put to_Date around your null...

The null is assumed char, thus an implicit to_char is being placed around your date - hence the problem...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse