| 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
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 ?
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Jan 11 2000 - 07:00:44 CST
 |
 |