Re: Y0K YEAR000 BETWEEN STATEMENT

From: Van Nieuwenhuyse <van_nieuwenhuyse_luytens_at_online.be>
Date: Tue, 8 Jun 1999 17:32:38 -0400
Message-ID: <F4155D9F9AA4D111B9990020AFBA52D5173E7D_at_ftp.sfi-software.com>


[Quoted] Don't worry guys, it 's a bug introduced in Oracle 6.3, and still there, and [Quoted] no intentions from Oracle to correct it. They simply don't accept the year 0, although there's a hole between the julian dates for the year 0.

Van Nieuwenhuyse heeft geschreven in bericht ...
>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 Tue Jun 08 1999 - 23:32:38 CEST

Original text of this message