This would seem to be pretty authoritative:
“And on the seventh day God ended his work which He had made; and he rested on the seventh day from all his work which He had made. And God blessed the seventh day, and sanctified it; because that in it he had rested from all his work which God created and made” (Genesis 2:2-3).
But, only in America.*
In many other NLS territories, Saturday is the sixth day of the week, not the seventh:
SQL> alter session set NLS_TERRITORY = 'AMERICA';
Session altered.
SQL> select to_char(sysdate+3,'D Dy') from dual;
TO_CH
-----
7 Sat
SQL> alter session set NLS_TERRITORY='UNITED KINGDOM';
Session altered.
SQL>select to_char(sysdate+3,'D Dy') from dual;
TO_CH
-----
6 Sat
Now that’s a real pain, because different Windows PCs will inevitably end up having different registry/environment variable settings for NLS_LANG (NLS_TERRITORY forms the middle part of the NLS_LANG variable). And NLS_LANG also gets set a lot in unix .profile’s. And those all override the databases settings.
So this week when I had users reporting different results from different PCs, I had initially assumed tnsnames.ora differences were pointing them off to different databases. But really it was because they were running code like this:
select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,'D') = 6 ;
The fix is to name the day of the week rather than count to it:
select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,'Dy') = 'Sat' ;
Essentially this issue makes the “D” date format unusable.
If code like the above absolutely couldn’t be changed, then a logon trigger would be the only way to set NLS_TERRITORY the same for all users:
create or replace trigger my_logon_trigger
after logon on database
begin
execute immediate 'alter session set nls_territory = ''UNITED KINGDOM'' ' ;
end ;
/
See Tom Kyte on NLS Date Format and asktom “language issue”
(more…)