Re: order by info_date

In article <slrn8k4du7.4kq.rzwartje_at_rob.home.nl>,   rzwartje_at_knoware.nl wrote:
> Hello there,
>
> probably this question has been asked already but couldn't find the
 answer so
> here it is again :)
> I have a field in my table wich contains the date as 'mm/dd/yy'. The
 data is
> inserted into oracle via to_date('12/29/99','mm/dd/yy'). Now I want
 to sort
> this data so that 01/01/99 comes before 01/02/99 ... 01/02/00 etc.
 When I use
> ( after much searching :) ) :
> order by info_date desc;
> I get:
> 01-feb-99
> 01-jan-99
> 01-apr-00
> 01-mar-00
> etc.
>
> The order I want is:
> 01-jan-99
> 01-feb-99
> 01-mar-00
> 01-apr-00
> The order function is working on 'yy' but also on 'mm'. How can I
 order desc on
> 'yy' and order asc on 'mm' and 'dd'.
>
> Thanks in advance for your time,
> Rob
>
>

it looks like info_date is a STRING, not a DATE and its sorting it as ascii text. without seeing the whole query -- you must be to_char'ing it in the query or its stored as text, not as a date -- i can tell you only to change:

order by info_date desc;

into

order by to_date( info_date, 'dd-mon-RR' ) desc;

make sure to use RR and try to stop using 2 characters years ;)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.