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

User · Answer

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.

User · Answer

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

User · Answer

You have the field in a string  so you ll need to convert it to datetime  order by CONVERT datetime  EventDate   desc

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server