Re: Order By problem with Dates

A copy of this was sent to rock_cogar_at_my-deja.com (if that email address didn't require changing) On Tue, 17 Aug 1999 20:15:42 GMT, you wrote:

>I have what should be an easy problem to solve.
>

It is -- always "order by s_date" no matter what you select. so:

select distinct to_char(to_date(substr(to_char(s_date),1,4)||'-'||substr (to_char(s_date),5,2)||'-'||substr(to_char(s_date),7,2),'YYYY-MM- DD'),'Mon DD, YYYY')
from tssds.ehefmsam
where locdesc = 'MW-8'
order by S_DATE asc;

You do not need to SELECT a column to order by it - result sets can be ordered by any column(s) or functions of those column(s)...

>Column s_date is type number that is initialized with values to store
>dates. Yeah, I know it is dumb, but those were the rules I was to
>follow. Here is a simple select and the result set:
>
>select distinct s_date
>from tssds.ehefmsam
>where locdesc = 'MW-8'
>order by 1 asc;
>
>S_DATE
>----------
> 19911203
> 19920121
> 19920227
> 19920617
> 19920717
> 19921016
> 19950126
> 19950422
> 19950716
> 19951017
>
>The programmers want to select a bunch of columns from this table and
>sort them by date and return the results as text for use in Cold Fusion
>web applications. So the following at least gets the data into the
>default date format and is sorted correctly.
>
>select distinct to_date(substr(to_char(s_date),1,4)||'-'||substr(to_char
>(s_date),5,2)||'-'||substr(to_char(s_date),7,2),'YYYY-MM-DD')
>from tssds.ehefmsam
>where locdesc = 'MW-8'
>order by 1 asc;
>
>TO_DATE(S
>---------
>03-DEC-91
>21-JAN-92
>27-FEB-92
>17-JUN-92
>17-JUL-92
>16-OCT-92
>26-JAN-95
>22-APR-95
>16-JUL-95
>17-OCT-95
>
>But if I convert the dates to text using:
>
>select distinct to_char(to_date(substr(to_char(s_date),1,4)||'-'||substr
>(to_char(s_date),5,2)||'-'||substr(to_char(s_date),7,2),'YYYY-MM-
>DD'),'Mon DD, YYYY')
>from tssds.ehefmsam
>where locdesc = 'MW-8'
>order by 1 asc
>
>TO_CHAR(TO_D
>------------
>Apr 22, 1995
>Dec 03, 1991
>Feb 27, 1992
>Jan 21, 1992
>Jan 26, 1995
>Jul 16, 1995
>Jul 17, 1992
>Jun 17, 1992
>Oct 16, 1992
>Oct 17, 1995
>
>
>I end up with the list sorted by month / day / year, rather than
>by "date" ( year / month / day).
>
>Any ideas on getting this date list back as correctly sorted text ?
>
>Thanks
>Rock Cogar.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
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 Wed Aug 18 1999 - 06:41:13 CDT