Re: Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 15 Nov 2011 15:54:05 +0000
Denis,
On 15/11/11 14:59, Denis wrote:
> SQL Plus does not throw error in the case below:
>
> SQL> alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';
> Session altered.
> SQL> select count(*) from dba_objects where created between '1-Nov-2011'
> and '14-Nov-2011';
> COUNT(*)
> ----------
> 0
Very interesting. This "proves" my point that you should be comparing
dates with dates and not dates with strings.
I'd personally be making any conversion that takes place explicit, as
follows:
SQL> select count(*)
from dba_objects
where created between to_date('01-Nov-2011', 'dd-Mon-yyyy') and
to_date('14-Nov-2011', 'dd-Mon-yyyy');
Then, regardless of the deafult date format in use, Oracle knows to
compare dates with dates.
I rather suspect the above modification will return the correct number
of rows.
I know that SQL*Plus does attempt to convert to a date when the default
fails, but I'm not able to remember which different formats it tries.
Thankfully you are using month names rather than numbers - which would
be interesting to know how Oracle converts '07/04/2011' - is it April or
July?
having fallen over implicit conversions before, my advice to any
developer or DBA is "always be explicit".
> SQL> alter session set nls_date_format='dd-Mon-yyyy HH24:mi:ss';
> Session altered.
> SQL> select count(*) from dba_objects where created between '1-Nov-2011'
> and '14-Nov-2011';
> COUNT(*)
> ----------
> 2987
This worries me. By allowing implicit conversions to take place (or
apparently not in some cases) how do you know that any of your date
based selects are actually working?
It's too dangerous to avoid being explicit by the look of things,
especially given your comments that Sql*Plus does one thing and SQL
Developer another - when presented with the same SQL statement.
Too dangerous!
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767
--
//www.freelists.org/webpage/oracle-l
Other related posts:
