Giridhar wrote:
> Hi,
> Please review the following two pl/sql blocks.
> Our NLS_DATE_FORMAT is defined as DD-MON-RR.
> ------------------------------------------------------------
> SQL> declare
> 2 v date;
> 3 begin
> 4 select decode(0,-1,NULL,(to_date('05251932', 'MMDDYYYY'))) into v
> from
> dual;
> 5 DBMS_OUTPUT.PUT_LINE(to_char(v,'DD-MON-YYYY'));
> 6 end;
> 7 /
> -----------
> 25-MAY-2032
> -----------
> PL/SQL procedure successfully completed.
>
> SQL> declare
> 2 v date;
> 3 begin
> 4 select to_date('05251932', 'MMDDYYYY') into v from dual;
> 5 DBMS_OUTPUT.PUT_LINE(to_char(v,'DD-MON-YYYY'));
> 6 end;
> 7 /
> -----------
> 25-MAY-1932
> -----------
> PL/SQL procedure successfully completed.
> ------------------------------------------------------------
>
> May i know why the first one is displaying value as 25-MAY-2032 instead
> of
> 25-MAY-1932
>
> Thanks
> Giridhar
>
Because you have a decode, and that does (datatype) conversions.
"Oracle7 automatically converts the return value to the same
datatype as the first result."
Your first result is -1, the outcome of the decode would therefore
become numeric. As your assignment datatype is date, a conversion
will take place, and that will go through a varchar2. As this is
an implicit conversion, the default date format mask is applied,
"DD-MON-RR".
As case statement would prevent this, but isn't available in Oracle7.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Fri Dec 30 2005 - 05:48:11 CST
