Re: R: Date Data Type in Index

In article <7u51uf$orm$1_at_nslave1.tin.it>,   "Riccardo Ferrari" <riccardo.ferrari_at_informatica2.it> wrote:
> > If you write
> > SELECT * FROM tab_a
> > WHERE entry_date = TO_DATE('09101999','DDMMYYYY');
> > No rows will be retrieved.
>
> Why? It depends on the inserted value.If you had iussed an insert
stmt such
> as
> INSERT INTO tab_a (entry_date) VALUES
> TO_DATE('09101999','DDMMYYYY');
> then the query retrieves it.
>

It exactly what I wrote :
"You have either to truncate time portion on INSERT/UPDATE or
  SELECT * FROM tab_a
  WHERE entry_date = BETWEEN
  TO_DATE('09101999000001','DDMMYYYYhh24miss') AND   TO_DATE('09101999235959','DDMMYYYYhh24miss') ;"

HTH. Michael.

> In general the problem on date field retrieval is related to the
meaning of
> the field itself. For example, if you meet conditions such as
> - you deal with date field with 'day' level precision
> - you store it in the DB with the 'time' part = 00:00:00
> - your queries are mostly concerned with day level comparisons
> then an index on this field will be used and usefull.
> If, for example the second condition cannot be guaranteed, but the
other two
> are still valid, you have to truncate the DB date to compare it with
the
> result of no index use.
>
> To summarize: it depends on your application!
>
> Regards
> Ricardo
>
> <michael_bialik_at_my-deja.com> wrote in message
7tnafr$8nm$1_at_nnrp1.deja.com...
> > Hi.
> >
> > The only thing I can think about is that Oracle DATE
> > is actually DATE & TIME format.
> > It means that if you have something like that :
> >
> > CREATE TABLE tab_a (
> > entry_date DATE,
> > comment VARCHAR2 ( 30));
> > CREATE INDEX tab_a$entry_date on tab_a ( entry_date );
> >
> > Insert some data.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 16 1999 - 16:06:58 CDT