date sorting

[Guest_imported]

Can anyone advise how I can use "ORDER BY" to sort dates ?
At the moment I get

account date
X 01/10/2001
X 04/08/2001
X 06/06/2001

I know I can specify fieldname (date) or column number (2), but neither gives a true date sequence.
I am using SYBASE.
Many thanks in anticipation.

 

[fluteplr]

Are these actually stored as dates or are they dates stuck in a character field. If you are getting the wrong sort order I would guess the latter. Please post the result you are now getting and the actual datatype of the 'date' field.

 

[Guest_imported]

Hi
They are held in the tables as numeric fields CCYYMMDD.
I format them within the SELECT, and return them as DD/MM/CCYY.
But I need to present them in ascending order.
Thanks for your involvment.

 

[fluteplr]

I am not familiar with the exact sybase syntax, however what you want to do is sort on the unformatted field.

In t-sql (MS SQL Server) that would be

select convert(mydate, char(10), 112) as fdate from mytable
order by mydate

In sybase it may not let you sort on a field that is not included in the select in which case you will have to select the field both formatted and unformatted.

Also if sybase has an actual date field type, you might want to look at changing your database structure.