Re: Date Query y2k

In article <3888B8D5.80EBFDCF_at_cannontech.com>,   Aaron Lauinger <aaronl_at_cannontech.com> wrote:
> The following query works doesn't work after the year 2000 (it does
> before)
>
> select TO_CHAR(TimeStamp,'MM-DD-YYYY') from MyTable where
> TimeStamp >= TO_DATE('01-01-2000','MM-DD-YYYY') AND
> TimeStamp <= TO_DATE('01-02-2000','MM-DD-YYYY')
>
> Upon closer inspection, it appears as though the date
> TO_DATE('01-01-2000','MM-DD-YYYY') is greater than any
> date in the database, including dates after 1/1/2000.
>
> The next query is the same as the first except the first date is in
1999
> and the second in 2000
>
> select TO_CHAR(TimeStamp,'MM-DD-YYYY') from MyTable where
> TimeStamp >= TO_DATE('12-31-1999','MM-DD-YYYY') AND
> TimeStamp <= TO_DATE('01-01-2000','MM-DD-YYYY')
>
> This query returns every row after 12/31/1999 since oracle is treating
> TO_DATE('01-01-2000','MM-DD-YYYY')
> as very big.
>
> Does someone have insight as to why this is?
> btw i'm using 8.0.4.
>
> Greetings, Aaron,

  My first suggestion is to check your NLS_DATE_FORMAT in the   database - I just spent the worst week of my life having to   recreate a database to get a table back (it's a long story)   because the NLS_DATE_FORMAT was set to 'DD-MON-YY' instead of   either 'DD-MON-RR' or 'DD-MON-YYYY'.

  NLS_DATE_FORMAT can be set for a session, or permanently   set in the init.ora.

  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.

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

  Regards

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jan 22 2000 - 12:07:40 CST