TRUNC(sysdate, 'day') output different on local vs application oracle

[Sandeep Kumar]

At UNIX or OpenVMS server (which is installed by Oracle DBA and on which my project application is running) if I run
select TRUNC(sysdate, 'day') from dual
Output is 5th Sept 2011 (Monday)

I have checked onhttp://www.techonthenet.com/oracle/functions/trunc_date.php and as per that it should be 5th Sept 2011 (Monday)

But if I run same query on PL/SQL developer or Toad or Oracle SQL plus output (which are installed on windows env and have Oracle 9i installed by me on my local machine) is 4th Sept 2011 (Sunday)

Can you please suggest why on PL/SQL developer I am getting 4th Sept 2011 (Sunday)?
Also, can you please advise if there is some setting which I should make in windows so that I get output same as UNIX or OpenVMS OS?

 

[Dezsoe Kados]

I suspect that the starting day of the week depends on (possibly client) NLS settings...
At least I can reproduce your described behaviour in "pure" SQL*Plus, sugesting this is no PLSD issue.
If really you want to truncate to the same day use trunc(sysdate,'DDD')

 

[Sandeep Kumar]

Dezsoe many thanks for your kind advise.

Using that I am able to resolve my problem:
To get start of week as Monday i.e. same setting as server on which application is installed then following can be used:

ALTER SESSION SET NLS_TERRITORY='FRANCE';
select trunc(sysdate, 'day') from dual;
Output: 5th Sept 2011 (Monday)

To determine other nls_territory values,
select value, isdeprecated
from v$nls_valid_values
where parameter='TERRITORY'