OraFAQ Forum: SQL & PL/SQL » TRUNC(sysdate, 'day') output different on local vs application oracle

Home » SQL & PL/SQL » SQL & PL/SQL » TRUNC(sysdate, 'day') output different on local vs application oracle (Oracle 9i, Windows XP, Open VMS OS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TRUNC(sysdate, 'day') output different on local vs application oracle [message #522046]

Tue, 06 September 2011 01:51

destinysandeep
Messages: 3
Registered: September 2011
Location: Mumbai

Junior Member

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 on techonthenet 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, Toad, Oracle SQL plus 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?

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522051 is a reply to message #522046]

Tue, 06 September 2011 02:28

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SYSDATE returns database server's system date. So, your TOAD connects Oracle 9i which is installed on your own PC. This PC is Oracle 9i's database server. It appears that date set on your PC is 4th Sep 2011. Verify it (desktop's right bottom corner).

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522076 is a reply to message #522051]

Tue, 06 September 2011 06:34

destinysandeep
Messages: 3
Registered: September 2011
Location: Mumbai

Junior Member

No, Date set on my machine (Windows XP) and testing server (Open VMS) are same. Even time is same i.e. London.
There is some other issue as on my colleague machine output's are ok but we are not able to figure out setting!

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522079 is a reply to message #522076]

Tue, 06 September 2011 06:45

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you just select sysdate (without trunc) do you get the correct time?

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522095 is a reply to message #522076]

Tue, 06 September 2011 07:44

destinysandeep
Messages: 3
Registered: September 2011
Location: Mumbai

Junior Member

Lucky, I am able to resolve it:
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'

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522097 is a reply to message #522095]

Tue, 06 September 2011 08:04

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

In other words - your PC thinks it's in a different location to everything else.

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522104 is a reply to message #522051]

Tue, 06 September 2011 08:47

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Littlefoot wrote on Tue, 06 September 2011 03:28

SYSDATE returns database server's system date. So, your TOAD connects Oracle 9i which is installed on your own PC. This PC is Oracle 9i's database server. It appears that date set on your PC is 4th Sep 2011. Verify it (desktop's right bottom corner).

Well, before building a "theory" it would help to check what TRUNC(dt,'day') does. It returns starting day of the week. And guess what, yes starting day of the week is CLIENT nls settings dependent. The rest is in destinysandeep's reply.

Report message to a moderator

Re: TRUNC(sysdate, 'day') output different on local vs application oracle [message #522133 is a reply to message #522104]

Tue, 06 September 2011 14:44

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Above all, it would help to actually read what the question was. My apologies.

Report message to a moderator

Previous Topic:	Suppressing ORA-06512 & ORA-04088 when using RAISE_APPLICATION_ERROR?
Next Topic:	about privileg

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 17 18:59:36 CDT 2025