NLS parameter's priority?

[ericfjchen]

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

[Jean-Luc Oudart]

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

[Yogeeraj_1 · Accepted solution]

hi eric,

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

hope this helps!
regards
yogeeraj

[Yogeeraj_1]

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

[Eric Antunes]

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

[Eric Antunes]

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

[Indira Aramandla]

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