Use of nls_date_language parameter in Oracle

There are some problems where your theoritical knowledge is not at all important. You need to go through such situation. Read an example below..

You have an application whose users are all over the world. You are processing some files every day i.e. each day a new files comes in and you process that using the application available. Even in that, every day you handle one set of file - say for an example on Monday you handle file A, on Tuesday you handle file B, on Wednesday you handle file C etc.

Now issue here we faced was, at India or Germany or France for a particular day files which was getting picked up by the application was not same as application was picking file at US. We checked the date at both end and it was same. So what was fishing? please read below...
Now question is, how will you identify the day of a week - yaa simple SQL Query

 select to_char(sysdate,'D') from dual;

Looks pretty simple and result is quite obvious, it should be ???

We normally counts our week start day as Monday and Week end day as Sunday. so if today is 7-Jan-2009 which is wednesday, the above query will or should return 3 i.e. thid day of a week.

But this does not ends here...Please go through below queries

I have checked the nls_territory parameter value of my db, which results into AMERICA.

SQL> connect
Enter user-name: sys as sysdba
Enter password:
Connected.
SQL> show parameter nls_territory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_territory                        string      AMERICA
SQL>
SQL> select to_char(sysdate,'D') from dual;
T
-
4

Above query gave me the result as 4, so I varified the sysdate.

SQL>  select to_char(sysdate, 'dd-mon-yyyy HH:MI:SS AM') from dual;
TO_CHAR(SYSDATE,'DD-MON'
-----------------------
07-jan-2009 01:26:14 AM

SQL> select to_char(sysdate,'D') from dual;
T
-
4
SQL>

SQL> select to_char(sysdate, 'Day') from dual;
TO_CHAR(S
---------
Wednesday
SQL>

So, it showed me that, its a 7-Jan-2009, Wednesday and Week Day is 4

Now, I changed my parameter - nls_territory - value to FRANCE

SQL> ALTER SESSION SET NLS_TERRITORY = France;
Session altered.

I again verify my above values

SQL> select to_char(sysdate,'D') from dual;
T
-
3

SQL> select sysdate from dual;
SYSDATE
--------
07/01/09

SQL> select to_char(sysdate, 'dd-mon-yyyy HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-'
--------------------
07-jan-2009 01:22:06

SQL> select to_char(sysdate, 'Day') from dual;
TO_CHAR(S
---------
Wednesday

What does it means?

For 7-Jan-2009, wednesday, if US is a territory Day of a week is 4 but if FRANCE as a territory Day of a week is 3; mmm quite weired, isnt it?


We can try it out using nls_date_language..


SQL> alter session set nls_date_language=GERMAN;
Session altered.

SQL> select to_char(sysdate, 'Day') from dual;
TO_CHAR(SY
----------
Mittwoch

SQL> alter session set nls_date_language=AMERICA;
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

SQL> alter session set nls_date_language=AMERICAN;
Session altered.

SQL> select to_char(sysdate, 'Day') from dual;
TO_CHAR(S
---------
Wednesday
SQL>


This means that, even if we try using Day instead of D, results are different in both cases i.e at US and at Germany.

Try using below now...

SQL> alter session set nls_date_language = GERMAN;
Session altered.

Solution to this problem is, when you are calculating such values always use below to_char function

SQL> select to_char(sysdate, 'Day', 'nls_date_language = GERMAN') from dual;
TO_CHAR(SY
----------
Mittwoch

SQL> select to_char(sysdate, 'Day', 'nls_date_language = AMERICAN') from dual;
TO_CHAR(S
---------
Wednesday
SQL>

Yesss, this works,

So, the thumb rule is, where ever you are located, if you are working with date and timezones are different, be specific in forming a query and use nls_date_language of your query same globally. as mentioned below.

We do not know, why week day at US starts from Sunday and rest of the world from Monday.