Re: Date Query y2k

attwoody wrote:

> I On January 13, 2000, I executed this query:
>
> delete from name-of-table
> where date-on-table < '03-JUL-99';
>
> What I _wanted_ was to purge all the records earlier than
> July 3, 1999; what happened was that EVERYTHING was deleted
> because '13-JAN-00' was interpreted as being less than
> '03-JUL-99'; ie, as a date in 1900 and not 2000.

I beg to differ. Actually, since you did not supply century in '03-JUL-99' and your NLS_DATE_FORMAT was set to 'DD-MON-YY', Oracle supplied the missing part (eg, the century) by deducting it from sysdate. So your dat got interpreted as 03-JUL-2099.

>
>
> When I executed a similar query in 1999, everything was
> just fine.

Yes, because the century supplied by default was then the 20th.

--

+-------------------------------------+----------------------------+
| Fred Petillot                       |     fpetillo_at_fr.oracle.com |
| Oracle France - Support Technique   |            +33 1 4762 8201 |
| 65, rue des Trois Fontanot          |            +33 6 8089 5135 |
| 92732 Nanterre Cedex                |                            |
+-------------------------------------+----------------------------+