Home » SQL & PL/SQL » 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 Go to next message
namitanamburi
Messages: 18
Registered: March 2009
Junior 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 #595578 is a reply to message #595577] Wed, 11 September 2013 17:33 Go to previous messageGo to next message
BlackSwan
Messages: 22843
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
namitanamburi wrote on Thu, 12 September 2013 03:48
The 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 Go to previous messageGo to next message
Littlefoot
Messages: 19650
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
but TOAD acts differently and, actually, allows this (which is stupid, in my opinion): under "Configure TOAD options", navigate to "Data Grids" and "Data". In there you'll find date and time format select lists. Both of them allow ... huh, nothing to be selected, which causes data grid to display nothing as well, when dates are selected.

./fa/11103/0/
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 Go to previous messageGo to next message
cookiemonster
Messages: 10989
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 #595601 is a reply to message #595600] Thu, 12 September 2013 06:30 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Bug" as far as allowing no date & time format to be set (see the screenshot once again). Otherwise, it works just OK in TOAD as well.
Re: Date result different for same query in Toad and SQL Developer [message #595602 is a reply to message #595601] Thu, 12 September 2013 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm assuming the OPs TOAD is capable of displaying some dates.
If not then it's what you suggested.
If so then it's probably a bug in their version that doesn't exist in yours.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Thu, 12 September 2013 16:11
Which 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 #595608 is a reply to message #595605] Thu, 12 September 2013 07:32 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I read it differently. The first two SELECTs were executed in two different tools: one in SQL Developer, another in TOAD.

The last two SELECTs were both executed in SQL Developer, as the OP meant to show that date column really contains some value, for both P_IDs (1 and 2).

Anyway: we'd probably better stop guessing and wait for namitanamburi's reaction (if any).
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 Go to previous messageGo to next message
cookiemonster
Messages: 10989
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 #595620 is a reply to message #595600] Thu, 12 September 2013 08:47 Go to previous messageGo to next message
EdStevens
Messages: 295
Registered: September 2013
Senior Member
cookiemonster wrote on Thu, 12 September 2013 06:21
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.
<snip>


Until such time as the GUI has to display the date on the screen. Then it must convert the date (a binary, internal format) into some string of characters, and so must use some formatting rule that it gets from somewhere.
Re: Date result different for same query in Toad and SQL Developer [message #595621 is a reply to message #595620] Thu, 12 September 2013 08:51 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some rule from somewhere yes, but not the nls_date_format (unless you tell it to, if you can, doesn't look like TOAD lets you). It's the GUI doing the formatting, not the DB and it has its own formatting settings.
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 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Thu, 12 September 2013 13:24
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.


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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
cookiemonster wrote on Thu, 12 September 2013 19:24
Lalit Kumar B wrote on Thu, 12 September 2013 13:24
What 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 #595689 is a reply to message #595657] Thu, 12 September 2013 14:05 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Two different tools: TOAD at work, SQL Developer at home.
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 Go to previous messageGo to next message
namitanamburi
Messages: 18
Registered: March 2009
Junior 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



Re: Date result different for same query in Toad and SQL Developer [message #595887 is a reply to message #595803] Sun, 15 September 2013 18:12 Go to previous message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
So to be clear - most dates display in toad?
Check what this gives:
SELECT to_date('09-MAY-5249', 'DD-MON-YYYY') from dual;

If that works in toad then you've got corrupt data.
Previous Topic: Question Regarding Line Numbering For SQL Developer
Next Topic: how to run all sql scripts from one script
Goto Forum:
  


Current Time: Wed Oct 01 10:42:57 CDT 2014

Total time taken to generate the page: 0.09437 seconds