Home » SQL & PL/SQL » SQL & PL/SQL » Different day result from PL/SQL and SQL
Different day result from PL/SQL and SQL [message #244601] Wed, 13 June 2007 08:42 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
I'm getting back a diffrent day of the week when using PL/SQL compare to SQL

n_dayofweek number;

n_dayofweek:=to_char(to_date(rc1.avg_parsed_date,'dd/mm/yyyy'),'d')

the n_dayofweek is set to 3

However if I do select to_char(avg_parsed-date,'d')from table1 then I will get back 5 which is the correct result.

Does anyone know why?
Re: Different day result from PL/SQL and SQL [message #244605 is a reply to message #244601] Wed, 13 June 2007 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Different NLS parameters between server and client.

Regards
Michel
Re: Different day result from PL/SQL and SQL [message #244611 is a reply to message #244605] Wed, 13 June 2007 08:57 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
Can't be. I'm running both using the Oracle SQL*Plus client.
Re: Different day result from PL/SQL and SQL [message #244619 is a reply to message #244611] Wed, 13 June 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But PL/SQL runs on the server.
Post a test case that we can reproduce.
Post your NLS settings for both client and server.

Regards
Michel
Re: Different day result from PL/SQL and SQL [message #244631 is a reply to message #244601] Wed, 13 June 2007 10:00 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
how do you find out the NLS setting?

The SQL is embedded in the PLSQL procedure so it should be running on the server
Re: Different day result from PL/SQL and SQL [message #244638 is a reply to message #244631] Wed, 13 June 2007 10:20 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
This is the NLS setting

SQL> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Re: Different day result from PL/SQL and SQL [message #244647 is a reply to message #244638] Wed, 13 June 2007 11:09 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same thing from database and instance.
Please read and apply How to format your posts.

Regards
Michel
Previous Topic: DATE functions in a weekly report
Next Topic: Problem with NUMBER datatype
Goto Forum:
  


Current Time: Fri Dec 09 13:58:11 CST 2016

Total time taken to generate the page: 0.10762 seconds