Y0K YEAR000 BETWEEN STATEMENT

[Van Nieuwenhuyse]

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.

[Alton Ayers]

Try this:
SELECT * FROM emp WHERE hiredate < TO_DATE('01010100','MMDDYYYY');

[Rick Losey]

Use "RR" function/format in your date manipulations

Rick Losey

Van Nieuwenhuyse wrote in message ...

[Van Nieuwenhuyse]

Don't worry guys, it 's a bug introduced in Oracle 6.3, and still there, and
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 ...

[Simon Hedges]

Van Nieuwenhuyse <van_nieuwenh...@online.be> wrote in message
news:F4155D9F9AA4D111B99...@ftp.sfi-software.com...

> Don't worry guys, it 's a bug introduced in Oracle 6.3, and still there,
and
> 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.

1 BC is immediately followed by 1 AD. Honest. There is no year Zero and
never has been. This is mainly because when the current calendar was
invented (in the 400s, I think), the concept of 0 was not fully development
in Western mathematics (which was still doing maths using Roman Numberals).

So the first millenium started, according to the current calendar, on 1 Jan
Year 1 (which is why the next millenium actually starts on 1 Jan 2001, and
not 1 Jan 2000).

Simon Hedges
Glouceester
UK