Andrew Fraser DBA

And on the seventh day, God fiddled with His NLS settings

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”

* I’m exaggerating: most countries follow American usage. It’s Europe which has the Saturday=6 convention, along with some of Europe’s former possessions: including Mexico (European numbering), but not Canada (American numbering). India, perhaps surprisingly, follows American rather than British day numbering. Israel sticks with the Biblical definition. For extra confusion, some Muslim countries set Friday as their seventh day:

SQL> alter session set NLS_TERRITORY = 'EGYPT';

Session altered.

SQL> select to_char(sysdate+3,'D Dy') from dual;

TO_CH
-----
1 Sat

But others (Turkey, Indonesia) go with European numbering.

Bottom line, keep the “D” date format out of your code.