Re: Date sort???

A copy of this was sent to Alex Peng <apeng_at_timecruiser.com> (if that email address didn't require changing) On Thu, 24 Jun 1999 10:44:35 EDT, you wrote:

>Hi,
>
>I use Oracle 8.0.4 in Windows NT.
>I have the following SQL :
>
>select publishtime from articles order by publishtime;
>
>Can anyone explain why year 98 comes after year 99?
>Thanks,
>

I have a table t:

SQL> create table t ( d date );
Table created.

and put some data into and ran the query:

SQL> select * from t order by d;

D

01-JAN-99
01-JAN-89
01-JAN-99

hmmm, 99 then 89, then 99 -- its not sorted... Wait, lets look at that data:

SQL> insert into t values ( to_date( '01-jan-1899' ) ); 1 row created.

SQL> insert into t values ( to_date( '01-jan-1999' ) ); 1 row created.

SQL> insert into t values ( to_date( '01-jan-1989' ) ); 1 row created.

ahh -- there is the century, re-run the query with a date mask that shows us the century:

SQL> select to_char(d,'dd-mon-yyyy') from t order by d;

TO_CHAR(D,'

01-jan-1899
01-jan-1989
01-jan-1999

I'll bet your 98 data is 2098 or your 99 data is 1899 or (even worse) your 99 data is 0099.... use to_char() to see whats really happening.

>Alex
>
>The result is : (some of records return null)
>
>28-MAY-99
>28-MAY-99
>28-MAY-99
>31-MAY-99
>31-MAY-99
>31-MAY-99
>01-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>04-JUN-99
>06-JUN-99
>06-JUN-99
>06-JUN-99
>06-JUN-99
>07-JUN-99
>07-JUN-99
>08-JUN-99
>08-JUN-99
>08-JUN-99
>08-JUN-99
>08-JUN-99
>09-JUN-99
>14-JUN-99
>14-JUN-99
>14-JUN-99
>14-JUN-99
>14-JUN-99
>14-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>15-JUN-99
>21-JUN-99
>21-JUN-99
>21-JUN-99
>21-JUN-99
>21-JUN-99
>21-JUN-99
>21-JUN-99
>21-JUN-99
>22-JUN-99
>22-JUN-99
>22-JUN-99
>22-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>23-JUN-99
>01-FEB-98
>01-FEB-98
>01-FEB-98
>01-FEB-98
>01-FEB-98
>01-FEB-98
>01-FEB-98
>01-FEB-98
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>107 rows selected.
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 24 1999 - 09:52:09 CDT