cancel
Showing results for 
Search instead for 
Did you mean: 

NLS parameter's priority?

SOLVED
Go to solution
ericfjchen
Regular Advisor

NLS parameter's priority?

If we set NLS_DATE_LANGUAGE & NLS_DATE_FORMAT=DD-MON-RR, their date format are not the same. Which parameter will have high priority?

Thanks

Eric
6 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: NLS parameter's priority?

Eric

see Metalink
Note:74375.1
Subject: Default Date Formats

see the example :
SVRMGR> select * from nls_session_parameters 2> where parameter in ('NLS_DATE_LANGUAGE', 'NLS_DATE_FORMAT'); PARAMETER VALUE ------------------------------ ------------------------------ NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE AMERICAN SVRMGR> select sysdate from dual; SYSDATE --------- 28-SEP-99 SVRMGR> alter session set nls_date_language=polish; SVRMGR> select * from nls_session_parameters 2> where parameter in ('NLS_DATE_LANGUAGE', 'NLS_DATE_FORMAT'); PARAMETER VALUE ------------------------------ ------------------------------ NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE POLISH SVRMGR> select sysdate from dual; SYSDATE --------- 28-WRZ-99 SVRMGR> alter session set nls_language=hungarian; SVRMGR> select * from nls_session_parameters 2> where parameter in ('NLS_DATE_LANGUAGE', 'NLS_DATE_FORMAT'); PARAMETER VALUE ------------------------------ ------------------------------ NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE HUNGARIAN SVRMGR> select sysdate from dual; SYSDATE --------- 28-SZE-99

Regards
Jean-Luc
fiat lux
Yogeeraj_1
Honored Contributor
Solution

Re: NLS parameter's priority?

hi eric,

have a look at metalink note: 241047.1 - The Priority of NLS Parameters Explained

hope this helps!
regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: NLS parameter's priority?

hi again,

section 6. explains it all


If the NLS_LANG is set (either like in point 3,4 or 5) then parameters like NLS_SORT, NLS_DATE_FORMAT,... can be set as a "standalone" setting and will overrule the defaults derived from NLS_LANG _ part.


hope this helps!
regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: NLS parameter's priority?

Hi Eric,

Those parameters are 2 different concepts. NLS_DATE_FORMAT is the date format that will be used by default. NLS_DATE_LANGUAGE is the language that will be used by default to display those dates.

For example:

select to_char(sysdate - 7,'DD-MON-RR', 'nls_date_language=american') date1,
to_char(sysdate - 7,'DD-MON-RR', 'nls_date_language=portuguese') date2
from dual;

Will return "DEC" for American language and "DEZ" for Portuguese language:

DATE1 DATE2
----------- -----------
30-DEC-04 30-DEZ-04

For January the result will be the same: "JAN"...

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: NLS parameter's priority?

One more thing: each of these parameters can be overwriten at different levels:

NLS_DATE_FORMAT is used if a date format mask is not specified in application code. The effective NLS_DATE_FORMAT is determined by the following (in order of precedence):

1 - Session NLS_DATE_FORMAT (via ALTER SESSION command)
2 - Client side NLS_DATE_FORMAT (from client environment variables/registry settings)
3 - Instance NLS_DATE_FORMAT (from init.ora file)
4 - Database NLS_DATE_FORMAT

Eric Antunes
Each and every day is a good day to learn.
Indira Aramandla
Honored Contributor

Re: NLS parameter's priority?

Hi Eric,

There are 3 levels at which you can set NLS parameters:
Database, Instance and Session.

If a parameter is defined at more than one level then the rules on which one takes precedence are quite straighforward:
1. NLS database settings are overwritten by NLS instance settings
2. NLS database & NLS instance settings are overwritten by NLS session settings

If you have NLS_LANG set on the client then as a result of that you will also have a setting for NLS_DATE_FORMAT. Even if it's not set explicitly on the client, it will have a implicit value based on the default derived from the territory part of the NLS_LANG setting. That means that whatever setting you have in the init.ora gets "overwritten" (following rule '2' from above) by this setting from the client.

You can set the parameter NLS_DATE_FORMAT in the init.ora, but as we've seen in "question 1" above, if NLS_LANG is set in the client OS environment that has no effect because the "instance" setting in the init.ora is overwritten by the session setting coming from the environment variable. This is fully explained in the note 241047 as Yogeeraj mentioned.

Therefore, if NLS_LANG is set and you want a different NLS_DATE_FORMAT than is the default, you must also set NLS_DATE_FORMAT in the client environmentto override the default that is taken from the NLS_LANG.

Note: NLS_DATE_FORMAT set as a parameter in the OS environment will not take effect unless NLS_LANG is also set.

Attached is document explaining the difference between the YY,RR,YYYY and RRRR format masks.Metalink Note: 30557.1 explains the NLS_DATE_FORMAT and a Default Century.

Indira A

Never give up, Keep Trying