Re: NLS_DATE_FORMAT for Y2K

Hi Frank,
The problem is you are selecting a date from a table that of course somehow must have been inserted.
What you proof here is the nls_date_format was not DD-MON-RR at the time of insertion. Any date element you don't specify upon insertion will be taken from the sysdate, format rules being applied. Because of the incorrect format '19' has been inserted.
I have conducted similar test and it actually does work.

Hth,

Sybrand Bakker, Oracle DBA

Frank Siegel <NorthernSnow_at_worldnet.att.net> wrote in message news:7lj536$spa$1_at_bgtnsc01.worldnet.att.net...
> Thanks much for any help you may provide.
> I am running oracle 7.3.x.x and have set the nls_date_format to
dd-mon-rr.
> Here's the proof:
>
> > select substr(name,1,20), substr(value,1,20)
> from sys.v_$parameter where name like '%nls_date%'
>
> nls_date_format 2 DD-MON-RR
>
>
> I have created a 1 row, 1 column table:
> > desc test_y2k
> col1 date
>
> Here is all that is in the table:
> > select col1 from test_y2k;
> 05-JAN-11
>
> The "RR" is not acting as expected when I perform the following query. I
> thought I would get 2011
> and not 1911.
>
> >select DECODE(to_char(col1 , 'YYYY-MON-DD'),
> '2011-JAN-05' , '2011',
> '1911-JAN-05' , '1911',
> 'OTHER' )
> from test_y2k ;
>
> I get:
> 1911
>
> I get this even if I put RRRR instead of YYYY I get 1911. What's wrong!
> Thanks Again,
> Frank S.
> New Hampshire
>
>
>
>
>
>
>
Received on Fri Jul 02 1999 - 15:21:46 CDT