Re: Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk

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
--
http://www.freelists.org/webpage/oracle-l