Re: SQL and to_date problem

A copy of this was sent to Holger Kramer <hkramer_at_gmx.net> (if that email address didn't require changing) On Tue, 08 Sep 1998 11:49:49 +0200, you wrote:

>Hi!
>
>I want to query for all rows with a defined date:
>
>select count(*)
>from ia_artikel
>where to_char( ausgabedatum, 'DD.MM.YY' ) = '28.09.99';
>
>This works but the performance is poor. I think its because the
>conversation from date to char on each row.
>One other solution is to make a conversation on the static part:
>
>select count(*)
>from ia_artikel
>where ausgabedatum = to_date('28.08.99','DD.MM.YY');
>
>But this does not work! Why?

The dates have a time stored with them. The column ausgabedatum has DD-MON-YYYY HH24:MI:SS stored in it.

To query this and use the index, you may use something like:

select count(*)
from ia_artikel
where ausgabedatum between to_date('28.08.1999','DD.MM.YYYY')

                   and to_date('28.08.1999 23:59:59','DD.MM.YYYY HH24:MI:SS')

That will get all of the values on a given date without performing a function on the database column.

>
>Bye,
>Holger
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.