Order by descending date - month, day and year

Question

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.

Answer 1

I'm guessing EventDate is a char or varchar and not a date otherwise your order by clause would be fine.

You can use CONVERT to change the values to a date and sort by that

SELECT * 
FROM 
     vw_view 
ORDER BY 
   CONVERT(DateTime, EventDate,101)  DESC

The problem with that is, as Sparky points out in the comments, if EventDate has a value that can't be converted to a date the query won't execute.

This means you should either exclude the bad rows or let the bad rows go to the bottom of the results

To exclude the bad rows just add WHERE IsDate(EventDate) = 1

To let let the bad dates go to the bottom you need to use CASE

e.g.

ORDER BY 
    CASE
       WHEN IsDate(EventDate) = 1 THEN CONVERT(DateTime, EventDate,101)
       ELSE null
    END DESC

Answer 2

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.