SQL- order by date

[skthompson1977]

When I do an order by date I get the following:

03-DEC-04
08-DEC-04
01-JAN-05
11-NOV-04

What I want is:

11-NOV-04
03-DEC-04
08-DEC-04
01-JAN-05

Does anyone know how to do this?

Thanks

[greg.harvey]

Please read:

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

As ORDER BY Date should behave in the way you expect, I suspect your dates are corrupted - usually happens as a result of not using the International Date Time Format as detailed above - I am only guessing, but it's good practice anyway so I would read it and use the format if I were you...

[r937]

i'm guessing it's simpler than corrupted data

the date field is probably VARCHAR

[webjunkie]

Date field should not be VARCHAR.
You should use DATETIME. Otherwise, you are going
to have to do all kind of conversions. Beside the conversion problem,
you are going to have a difficult time with sorting and
comparing the date fields.
Define the date field as DATETIME, let
the client application retrieves the date field and does the necessary
conversion.