Re: Y0K YEAR000 BETWEEN STATEMENT

Use "RR" function/format in your date manipulations

Rick Losey

Van Nieuwenhuyse wrote in message ...
>I want to select all dates from a date-field in a table that contains 00 in
>the century.
>To avoid full table scan I did not do it this way: Select * from emp where
>SUBSTR(to_char(hiredate,'YYYY'),1,2) = '00';
>but with a between statement:
>Select * from emp where hiredate between to_date('01010000','DDMMYYYY') and
>to_date('01010100','DDMMYYYY') ;
>Oracle does not seem to like the '0' year, so this results in an error!
>BTW: following statement works perfectly but does not meet my needs: Select
>* from emp where hiredate between to_date('01010001','DDMMYYYY') and
>to_date('01010100','DDMMYYYY') ;
>
>Anyone a solution to get quickly(not full table scan) all the records with
>dates between the year 0000 and 0099.
>Note: the above example is made on the scott/tiger tables, of course I have
>an index on my date field.
>
>
Received on Fri Jun 04 1999 - 03:09:12 CEST