Re: Strange Date behaviour

andrewst wrote:
[snip]
>
> Try one of these instead:
>
> select * from OUR_WORK where
> trunc(WORK_DATE)=TO_DATE('31-MAR-2003','DD-MON-YYYY'); -- uses TRUNC
> function to ignore the time in WORK_DATE
>
> select * from OUR_WORK where WORK_DATE >=
> TO_DATE('31-MAR-2003','DD-MON-YYYY') and WORK_DATE <
> TO_DATE('01-APR-2003','DD-MON-YYYY');
>
> The second version, while more verbose, allows use of an index on
> WORK_DATE.
If you don't like the more verbose version, but yet want to use an index, you can create a 'function based index' ( since Oracle 8i if I recall correctly ).

CREATE INDEX work_trunc_date_idx ON OUR_WORK( trunc( WORK_DATE ) );

Now the first example should use an index as well.

Regards, Stephan

-- 
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born        | BRAIN FORCE SOFTWARE GmbH
                                     | Landsberger Allee 366
eMail: delete the capital letters   | 12681 Berlin
eMail: entferne die Grossbuchstaben | Germany
---------------------------------------------------------------
          PGP-Key verfügbar          |    PGP-Key available
---------------------------------------------------------------