Re: ORACLE Date Ranages and Y2K Bug

From: fumi <fumi__at_tpts5.seed.net.tw>
Date: Thu, 17 Dec 1998 22:45:02 +0800
Message-ID: <75d8fm$s1a$2@news.seed.net.tw>

dloper_at_my-dejanews.com ¼¶¼g©ó¤å³¹ <759oie$jfb$1_at_nnrp1.dejanews.com>...
>I am working on some reports that have user defined date ranges. I am
trying
>to Y2K proof them. I have succeeded in using the MM/DD/RR date format. I
can
>get the data when the date ranges are within the same century.
>
> Example:
> From Date: 12/01/1998 To Date: 12/31/1999
> From Date: 01/01/2000 To Date: 01/31/2001
>
>These date ranges work fine. The problem developes when I input a date
range
>in like the following: From Date: 12/01/1998 To Date: 01/31/2001
>
>The program can't find the transactions when I cross the into the year 2000
>from the 1900's.
>
>Has anyone ever come up against this problem? I would appreciate any help
you
>can give.

There is nothing wrong with date format 'MM/DD/RR'. I think, the only possibility is your misusage on date data type.

The following scrpit show it:

SQL> create table test_d (d date);

Table created.

SQL> begin
  2 for i in 1..100 loop
  3 insert into test_d values (sysdate+i*10);   4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='MM/DD/RR';

Session altered.

SQL> select * from test_d where d between '12/01/98' and '01/31/01';

D

---

12/27/98
....[snip]
01/15/01
01/25/01

77 rows selected.

See, Oracle finds the result correctly across year 2000. Received on Thu Dec 17 1998 - 08:45:02 CST