nls_date_format

[Knarayan]

I am having a very strange issue, i added the following line to my init.ora and restarted the database:
NLS_DATE_FORMAT='MM/DD/YYYY'
after this from the telnet unix window sqlplus i am getting the following:
select sysdate from dual;
SYSDATE
----------
04/18/2002
and
select * from v$nls_parameters
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
MM/DD/YYYY

but when I connect to the same database from sqlplus on my machine here is what i get:
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
select sysdate from dual;
SYSDATE
---------
18-APR-02

why are these different on my local machine???
but in the view v$parameter it shows the changed value:
mm/dd/yyyy
Please advise

[Shestakov]

Exists 2 level of NLS in oracle.
1. On server side, NLS may be setup in init.ora file (NLS_* parameters)
2. on client side (!! for each client mashine seperatly)
NLS parameters setup in:
-- unix client -- environment variables
-- winNT, Win9x -- in register (Oracle section)
U should check NLS_DATE_FORMAT or NLS_LANG parameters on ur masine.

If NLS_DATE_FORMAT don't setup then oracle impement default DATE_FORMAT from NLS_LANG (part territory)

[ramaral]

The date format is stord in the registry when the software was installed on your machine.

It will usually be found in

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME

It may be that there will be 2 entries so look for that OR there may not be an entry in which case you will need to enter.

As for the reason, I think it was because of the different format requirements needed for the clients as opposed to the server.

[pando]

hmm rather than two levels I guess it's three

database level
instance level
client side

[Shestakov]

Originally posted by pando
hmm rather than two levels I guess it's three

database level
instance level
client side

Yes, sure, if we have OPS or RAC.
In this case we have 3 levels for all NLS parameters, exept "semi-NLS" parameter --> characterset.
-------------------------------------------
I not reccomed setup different NLS parameters for different instance of OPS.
It may create big problems with sort operations in parallel mode.

[Edited by Shestakov on 04-18-2002 at 10:23 AM]

[Knarayan]

Thanx all of you for your replies, in registry i have only nls_lang set so that means the date_format is derived from the nls_lang value right?
I changed the nls_date_format as users were getting ora 1801 date format is too long for internal buffer.. will the change in server (init.ora) solve this issue..

[pando]

every country has some default nls parameter values so most of the times it is enough setting nls_lang to get the values or format you want

so it is correct saying that your nls_date_format is derived from nls_lang

if you change nls_date_format in your init.ora it wont solve the problem because at client side you have your nls settings the order looks like this

if you set nls parameters at client the it rules otherwise instance setting rules if not set neither client and instance then database level rules

client first
instance second (init.ora)
database third

setting init.ora would work if you get rid of your nls_lang in client's registry

[Knarayan]

Got it!
Thanx guys..