Internal Date Issue

From: J Mike Rollins <rollins_at_wfu.edu>
Date: 11 Jan 2000 00:14:59 GMT
Message-ID: <85dsm3$p2c@f1n1.spenet.wfu.edu>

We did a select order by date and found that our records from y2k were not sorted by date correctly:

	03-JAN-00
	04-JAN-00
	04-JAN-00
	08-JAN-00
	09-JAN-00
	02-JAN-00
	03-JAN-00
	09-JAN-00

I then did a little more investigating and displayed the internal oracle date information.

select dump(date_time_in) internal, date_time_in   from student_hours
 order by date_time_in;

INTERNAL                            DATE_TIME
----------------------------------- ---------
Typ=12 Len=7: 119,200,1,3,9,51,1    03-JAN-00
Typ=12 Len=7: 119,200,1,4,6,1,1     04-JAN-00
Typ=12 Len=7: 119,200,1,4,10,2,1    04-JAN-00
Typ=12 Len=7: 119,200,1,8,4,2,1     08-JAN-00
Typ=12 Len=7: 119,200,1,9,18,4,1    09-JAN-00
Typ=12 Len=7: 120,100,1,2,8,23,1    02-JAN-00
Typ=12 Len=7: 120,100,1,3,5,1,1     03-JAN-00

Typ=12 Len=7: 120,100,1,9,6,46,1 09-JAN-00

This shows that there are two ways for storing y2k dates internally.  Typ=12 Len=7: 119,200,1,3,9,51,1 03-JAN-00  Typ=12 Len=7: 120,100,1,3,5,1,1 03-JAN-00

The first 03-JAN-00 is stored as

	century = 100+19 
	year    = 100+100

The second 03-JAN-00 is stored as
	century = 100+20
	year    = 100+ 0

And it seems that the "order by date" is sorting these according to the century first, and then the year.

We think this might be due to an old classes111.zip JDBC drivers using the setDate function from a PreparedStatement.

Anyone got any ideas?
Should the year part of an oracle date be three digits?

Mike

      UNIX Systems Administrator at Wake Forest University.

---

          J. Mike Rollins              rollins_at_wfu.edu
     Wake Forest University     http://www.wfu.edu/~rollins

        Winston-Salem, NC            work: (336) 758-1938


======================================================================

Received on Mon Jan 10 2000 - 18:14:59 CST