Re: NLS_DATE_FORMAT and RR : Doesn't work !

A copy of this was sent to "Emmanuel Guyot" <Emmanuel.Guyot._at_wanadoo.fr> (if that email address didn't require changing) On Sat, 5 Jun 1999 12:47:49 +0200, you wrote:

>I've set NLS_DATE_FORMAT to dd-mon-rr instead of dd-mon-yy in my initxxx.ora
>in order to deal with Y2K problems. I've shutdowned and restarted the base

well, that won't deal with Y2K problems - it just changes the types of Y2K problems you'll have. Lets say your application is a database of 'fine wines'. Put in the year 49 and you probably don't mean 2049 but rather 1949. Lets say its a database with personnel records in it. Put in 45 and you might mean 2045 (persons retire date) or 1945 (persons birth date).

There is exactly one solution to y2k problems and that is YYYY. but anyway...

>(7.3.3) but it doesn't seem to works as I intented it to :
>
>select to_char(to_date('01-dec-01'),'dd/mm/yyyy') from dual;
>
>give the following output :
>
>01/12/1901
>
>Why ? Is the NLS_DATE_FORMAT only for output ?
>
>Thanks for your help.

If the client sets the NLS_* parameters -- they override the server in all cases. In fact, if the client sets the NLS_LANG parameter -- that causes all NLS_* settings on the server to be ignored and the defaults for that NLS_LANG specified on the client on used.

Where that comes into play is typically if the client is windows and the server is unix. The client install on windows set the NLS_LANG parameter in the registry by default. This setting is different from the default you find for unix. Any NLS_* settings you put in the init.ora will *not* be used by those clients.

You have to set the NLS_DATE_FORMAT in the registry on the client

or

you need to put an "alter session set nls_date_format='yyyymmdd'" in your apps right after the connect

or

with Oracle8i, release 8.1 you can code:

create or replace trigger data_logon_trigger after logon
ON DATABASE
begin

    execute immediate

        'alter session set nls_date_format = ''yyyymmdd'' '; end;
/

as well.

>
>E.G.
>
>-----------------------------------------------------------------------
>--------------------->>>> Emmanuel Guyot <<<<--------------------------
>8 Rue des Montées | Phone : [33] (0)2 38 56 42 72
>45100 Orléans | Email : emmguyot_at_altavista.net
>France | ICQ : 6945538
>-----------------------------------------------------------------------
>Home Page : http://perso.wanadoo.fr/emmanuel.guyot/belote
>-----------------------------------------------------------------------
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Jun 05 1999 - 09:11:35 CDT