CODEWITHSUNDEEP
sqlsql-server
user570457
user570457

Reputation: 585

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.

Upvotes: 57

Views: 482997

Answers (7)

Bhargav Variya
Bhargav Variya

Reputation: 765

Try this

SELECT     *
FROM  vw_view
ORDER BY DATE_FORMAT(EventDate, "%m-%d-%y") DESC

Upvotes: 0

John Petrak
John Petrak

Reputation: 2928

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.

Upvotes: 21

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 70

Martin Smith
Martin Smith

Reputation: 453288

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.

Upvotes: 7

Jason Jong
Jason Jong

Reputation: 4330

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

order by CONVERT(datetime, EventDate ) desc

Upvotes: 8

Kurru
Kurru

Reputation: 14331

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

Upvotes: 4

Robokop
Robokop

Reputation: 926

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

Upvotes: 4

Related Questions