Home » Other » Client Tools » Date result different for same query in Toad and SQL Developer (Toad and SQL Developer )
| Date result different for same query in Toad and SQL Developer [message #595577] |
Wed, 11 September 2013 17:18  |
namitanamburi
Messages: 35
Registered: March 2009
|
Member |
|
|
The date is different for same query in Toad and Sql Developer.
Below is the query
In SQL Developer
SELECT start_date, length(start_date) from my_table where p_id = 1;
09-MAY-5249 9
In Toad
SELECT start_date, length(start_date) from my_table where p_id = 1;
blank 9
Did some queries below for research.
SELECT start_date, DUMP(start_date, 1016)
from my_table
where p_id = 1;
09-MAY-5249 Typ=12 Len=7: 30,37,d5,a,0,a2,d5
SELECT start_date, DUMP(start_date, 1016)
from my_table
where p_id = 2;
01-JAN-2013 Typ=12 Len=7: 78,71,1,1,1,1,1
Why is same query returning two different results in two tools, please advise.
|
|
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595581 is a reply to message #595577] |
Thu, 12 September 2013 00:31  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
namitanamburi wrote on Thu, 12 September 2013 03:48The date is different for same query in Toad and Sql Developer.
Why is same query returning two different results in two tools, please advise.
Because, nls_date_format has an order of overriding precedence, and tool specific nls paramter settings will override the settings.
To be more clear on this, this is the most usual order of overriding precendence -
1. NLS_DATE_FORMAT is in the database initialization parameters, will be overriden by,
2. Settings of OS environment variable on the client machine, will be overriden by,
3. NLS parameter setting at session level with ALTER SESSION statements, will be overriden by,
4. to_date and to_char functions at the sql statement level.
Having said all that, in your situation, you need to check the NLS_DATE_FORMAT in both the tools.
Regards,
Lalit
[Updated on: Thu, 12 September 2013 02:31] Report message to a moderator |
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595599 is a reply to message #595581] |
Thu, 12 September 2013 05:41 |
 |
Littlefoot
Messages: 21783
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Which NLS_DATE_FORMAT causes any tool to return "blank" (as the OP reported) instead of a date?
SQL*Plus won't allow this (at least, I think so):SQL> alter session set nls_date_format = '';
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595600 is a reply to message #595599] |
Thu, 12 September 2013 06:21 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I doubt that that is the problem, I also doubt it's anything really to do with the nls_date_format.
The nls_date_format kicks in when converting a date to a string, either implicity or explicity without a format mask.
SQLPlus always converts dates to strings when you select them since it's a simple text interface and can't handle dates as dates.
Most GUIs can handle dates as dates though. When you select a date in TOAD for example it's fetched as a date into a date variable in the client (toad) code. So no implicit conversion happens and the nls_date_format is not applied.
The problem in this case is presumably due to the client code being unable to handle the particular year in this case. TOAD displays dates as calender objects in the data grid - that was probably never coded to go as high as 5249.
The length works of course because it outputs as number. However it's rather meaningless in this case since length only accepts char parameters.
So for that the date will be implicitly converted to a char using the nls_date_format.
So in a GUI if you select date, length(date) the results of the two can have nothing to do with each other, in sqlplus on the other hand they'll always correspond.
So it's probably a bug in TOAD.
For the record PL/SQL Developer displays that date correctly as well.
|
|
|
|
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595605 is a reply to message #595599] |
Thu, 12 September 2013 07:24 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Littlefoot wrote on Thu, 12 September 2013 16:11Which NLS_DATE_FORMAT causes any tool to return "blank" (as the OP reported) instead of a date?
Of course not, however, if you see OPs other two queries, it returns some date, and also the predicate condition for p_id is 2, rather than 1. If it shows blank for p_id=1, then why it displays "01-JAN-2013" for p_id=2. It must be blank for all records(while displaying of course).
So, bottomline is, it's not displaying blank always. What OP can do is, to cross-check, use alter session set nls_date_format in both the tools and check the output after executing the SQLs again.
|
|
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595613 is a reply to message #595608] |
Thu, 12 September 2013 07:48 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
We read it the same, I just assumed that the OP wouldn't have used 5249 in their example if any date did it.
Actually, I'm wondering if the first date is slightly corrupt:
SQL> CREATE TABLE testdate AS SELECT to_date('09-MAY-5249', 'DD-MON-YYYY') a FROM dual;
Table created.
SQL> select dump(a, 1016) from testdate;
DUMP(A,1016)
--------------------------------------------------------------------------------
Typ=12 Len=7: 98,95,5,9,1,1,1
From my understanding of how dump works that should give the same result as the OP got in his first dump result (the presence of time may change the last few digits, but not the first 4).
EDIT: hmmm didn't notice Lalit's last and thought LF was replying to me, not that it really changes my point.
[Updated on: Thu, 12 September 2013 08:53] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595622 is a reply to message #595605] |
Thu, 12 September 2013 08:54 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 12 September 2013 13:24What OP can do is, to cross-check, use alter session set nls_date_format in both the tools and check the output after executing the SQLs again.
As already noted TOAD ignores nls_date_format. It is probably worth selecting to_char(the date) and checking that gives the expected result - it ought to.
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595657 is a reply to message #595622] |
Thu, 12 September 2013 11:56 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
cookiemonster wrote on Thu, 12 September 2013 19:24Lalit Kumar B wrote on Thu, 12 September 2013 13:24What OP can do isnlo cross-check, use alter session set nls_date_format in both the tools and check the output after executing the SQLs again.
As already noted TOAD ignores nls_date_format. It is probably worth selecting to_char(the date) and checking that gives the expected result - it ought to.
What I contemplate is that, the to_char and to_date functions should override TOAD and any other tool, as well as, it should override the session level nls_date_format parameter. But the common problem is that users tend to focus on the displayed output, which varies in the way I already said. But Oracle internally MUST be correct.
One last thought, what makes anybody inclusing OP to use two different tools to get the output?
|
|
|
|
|
|
| Re: Date result different for same query in Toad and SQL Developer [message #595803 is a reply to message #595689] |
Fri, 13 September 2013 18:03 |
namitanamburi
Messages: 35
Registered: March 2009
|
Member |
|
|
Wow.........Now thats a lot to read from.
Appreciate your response.
1)To find out what caused the count to be 9 while the toad was displaying blank date, i ran the query in SQL Developer and voila it gave me a weird date.
2) For P_ID = 2 , there is a date, I ran the query so that I can give you an idea that the date field is valid and there is good data in that column.
3) first thing I checked before posting this is NLS Parameters in TOAD and SQL developer and it is DD-MON-RRRR for a date.
Thanks
Nammu
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Oct 03 03:08:42 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
