| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Internal Date Issue
On Tue, 11 Jan 2000, Connor McDonald wrote:
> J Mike Rollins wrote:
> >
> > 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
> > ======================================================================
>
> Try this:
>
> select to_char(datecol, 'dd-mon-yyyy') from table;
>
> Are all of the centuries the same ?
The following command prints the dd-mm-yyyy format, the Julian format, and Oracle's internal format. This statement orders by the actual oracle date field.
select to_char(date_time_in,'DD-MM-YYYY') MMDDYYYY,
to_char(date_time_in,'J') JULIAN,
dump(date_time_in) INTERNAL
03-01-2000 2451547 Typ=12 Len=7: 119,200,1,3,9,51,1 04-01-2000 2451548 Typ=12 Len=7: 119,200,1,4,6,1,1 04-01-2000 2451548 Typ=12 Len=7: 119,200,1,4,10,2,1 08-01-2000 2451552 Typ=12 Len=7: 119,200,1,8,4,2,1 09-01-2000 2451553 Typ=12 Len=7: 119,200,1,9,18,4,1 01-01-2000 2451545 Typ=12 Len=7: 120,100,1,1,1,2,1 02-01-2000 2451546 Typ=12 Len=7: 120,100,1,2,8,23,1 03-01-2000 2451547 Typ=12 Len=7: 120,100,1,3,5,1,1 05-01-2000 2451549 Typ=12 Len=7: 120,100,1,5,9,1,1 09-01-2000 2451553 Typ=12 Len=7: 120,100,1,9,6,46,1 09-01-2000 2451553 Typ=12 Len=7: 120,100,1,9,10,12,1 10-01-2000 2451554 Typ=12 Len=7: 120,100,1,10,1,1,1 10-01-2000 2451554 Typ=12 Len=7: 120,100,1,10,9,14,1 11-01-2000 2451555 Typ=12 Len=7: 120,100,1,11,1,56,1
However if I "order by" the Julian formatted date or the mm-dd-yyyy format date it sorts correctly. It I sort by the unformatted date then it orders the list as above.
Here is what I *think* is going on:
When oracle is asked to "order by (a formated date)" oracle does the math to compute the date in the specified format and then sorts the list. However, when oracle is asked to "order by (date type)" oracle then sorts the list quickly by sorting century first, year second, and so on. This requires no math. I come to this conclusion by observing that oracle does all the 119s are together and the 120s are together.
I wonder if Oracle specifications require the year part of the internal date storage to be less than 100. If this is the case then our JDBC drivers are not y2k compliant. (But, our new JDBC drivers are not handling long types correctly, but this is beside the point.) Received on Tue Jan 11 2000 - 16:25:32 CST
 |
 |