Order By problem with Dates

From: <rock_cogar_at_my-deja.com>
Date: Tue, 17 Aug 1999 20:15:42 GMT
Message-ID: <7pcft2$hoq$1@nnrp1.deja.com>

I have what should be an easy problem to solve.

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. Received on Tue Aug 17 1999 - 15:15:42 CDT