Re: Date Query y2k

From: Fred Petillot <fpetillo_at_fr.oracle.com>
Date: Mon, 24 Jan 2000 14:20:55 -0100
Message-ID: <388C6DD6.7260BEDF@fr.oracle.com>

Aaron Lauinger wrote:

> The following query works doesn't work after the year 2000 (it does
> before)

Script:

set echo on
spool tst
drop table MyTable;
create table MyTable(

TimeStamp date
);

insert into MyTable values (

to_date( '31-12-99','DD-MM-RR' )); insert into MyTable values (

to_date( '01-01-00','DD-MM-YY' )); insert into MyTable values (

to_date( '02-01-00','DD-MM-YY' )); insert into MyTable values (

to_date( '03-01-00','DD-MM-YY' ));

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'); spool off

gives:

SQL> drop table MyTable;
Table dropped.
SQL> create table MyTable(
  2 TimeStamp date
  3 );
Table created.
SQL> SQL> insert into MyTable values (
  2 to_date( '31-12-99','DD-MM-RR' )); 1 row created.
SQL> insert into MyTable values (
  2 to_date( '01-01-00','DD-MM-YY' )); 1 row created.
SQL> insert into MyTable values (
  2 to_date( '02-01-00','DD-MM-YY' )); 1 row created.
SQL> insert into MyTable values (
  2 to_date( '03-01-00','DD-MM-YY' )); 1 row created.
SQL>
SQL> select TO_CHAR(TimeStamp,'MM-DD-YYYY') from MyTable where   2 TimeStamp >= TO_DATE('01-01-2000','MM-DD-YYYY') AND   3 TimeStamp <= TO_DATE('01-02-2000','MM-DD-YYYY');

TO_CHAR(TI

01-01-2000
01-02-2000

SQL> spool off

So you should maybe check the actual date values stored in you

TimeStamp field.

+-------------------------------------+----------------------------+
| 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                |                            |
+-------------------------------------+----------------------------+

Received on Mon Jan 24 2000 - 09:20:55 CST