[sql] Order by descending date - month, day and year

This seems stupid but, I simply need a list of dates to be ordered with the most recent date at top. Using order by DESC doesn't seem to be working the way I want it to.

SELECT     *
FROM         vw_view
ORDER BY EventDate DESC

It gives me the date ordered by month and day, but doesn't take year into consideration. for example:

12/31/2009 

12/31/2008

12/30/2009

12/29/2009

Needs to be more like:

12/31/2009

12/30/2009

12/29/2009

12/28/2009

and so on.

what is the type of the field EventDate, since the ordering isn't correct i assume you don't have it set to some Date/Time representing type, but a string. And then the american way of writing dates is nasty to sort

Assuming that you have the power to make schema changes the only acceptable answer to this question IMO is to change the base data type to something more appropriate (e.g. date if SQL Server 2008).

Storing dates as mm/dd/yyyy strings is space inefficient, difficult to validate correctly and makes sorting and date calculations needlessly painful.

try ORDER BY MONTH(Date),DAY(DATE)

Try this:

ORDER BY YEAR(Date) DESC, MONTH(Date) DESC, DAY(DATE) DESC

Worked perfectly on a JET DB.

If you restructured your date format into YYYY/MM/DD then you can use this simple string ordering to achieve the formating you need.

Alternatively, using the SUBSTR(store_name,start,length) command you should be able to restructure the sorting term into the above format

perhaps using the following

SELECT     *
FROM         vw_view
ORDER BY SUBSTR(EventDate,6,4) + SUBSTR(EventDate, 0, 5) DESC

You have the field in a string, so you'll need to convert it to datetime

order by CONVERT(datetime, EventDate ) desc