Re: Regarding date formats

"Frank van Bortel" <frank.van.bortel_at_gmail.com> a écrit dans le message de news: dp365a$km1$1_at_news2.zwoll1.ov.home.nl...
| 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...

The first result is NULL (not -1) which is of VARCHAR2 datatype. This does not change the rest of your post: date->varchar2->date conversion and the application of implicit date format.

Regards
Michel Cadot Received on Fri Dec 30 2005 - 06:14:46 CST