Re: date format

Except that unless you have built a function based index this will be very inefficient and cause a full table scan. With the query I suggested the optimizer will turn the 2 to_dates into constants and not have to evaluate for each and every row in the database. Jim
"SATYA PAL" <spgangwar_at_yahoo.com> wrote in message news:a7b8a58b.0110291012.5dca3c3b_at_posting.google.com...
> Hi There,
>
> The best way to query on date is to use TRUNC function. Which will
> take the date part and exclude the time.
>
> SELECT * FROM TABLE1 WHERE TRUNC(DATE1) BETWEEN TRUNC(DATEFROM) AND
> TRUNC(DATETO);
>
>
> Regards
> Satya
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<DpeD7.12506$ib.5403807_at_news1.sttln1.wa.home.com>...
> > Try:
> > select * from table where date> =to_date('29.10.2001','DD.MM.YYYY') and
> > date<=to_date('30.10.2001','DD.MM.YYYY') ;
> > Otherwise make a function based index. Remember there is a time
 component to
> > dates.
> > Jim
> >
> > "andrija" <ar35644_at_fer.hr> wrote in message
> > news:9rj6tc$fitc$1_at_as201.hinet.hr...
> > > I have date column in a table and defined index on that column. When I
 query
> > > that table, I use 'where date between xxx and yyy'. Problem is that
 date
> > > column contains time also, which is not important to me.
> > > So if I use query like this:
> > >
> > > select * from table where to_char(date,'DD.MM.YYYY') ='29.10.2001'
> > >
> > > Does this query uses index? Should I create function based index? Is
 there
> > > any other easyer way? Can I force oracle to just ignore date in the
 column
> > > (because I've seen sometimes that time is not displayed in TOAD)?
> > >
> > >
Received on Tue Oct 30 2001 - 02:30:06 CET