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:

Needs to be more like:

and so on.

Why not updating you table design and make it correct, using a date field ?! Using workarounds for a poor design is not a good path. — iDevlop, May 30 '14 at 09:17
@ConradFrix one might argue that the tag `sql-server` presumes Microsoft SQL Server. That said, it would _still_ be nice to know what the datatype of that column is... — Gwyneth Llewelyn, Apr 20 '23 at 20:36
@GwynethLlewelyn seems form the question history I added the tag. Based on [this comment](https://stackoverflow.com/questions/4676139/order-by-descending-date-month-day-and-year?noredirect=1#comment5154912_4676158) I did it when the OP confirmed the DB. — Conrad Frix, Apr 24 '23 at 17:06
Oh! I stand corrected; sorry, I didn't check the history; I wrongly assumed that it was the OP who had self-tagged their answer. Thanks for your very valuable update :) — Gwyneth Llewelyn, May 05 '23 at 15:09

Conrad Frix · Accepted Answer · 2016-10-20T18:43:39.867

70

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

edited Oct 20 '16 at 18:43

answered Jan 13 '11 at 02:15

Conrad Frix

51,984
12
96
155

this works perfectly, thank you! you were correct in assuming it is sql server and EventDate is set as a varchar – user570457 Jan 13 '11 at 02:34
@user570457. No problem. I updated your tags to include SQL-Server. It a good idea to include it on the next question. – Conrad Frix Jan 13 '11 at 02:38
3

One caveat, if any of the dates are invalid, SQL will return an error on the convert, making your query invalid. Can be frustrating since you have to figure out which date is causing the problem. You can use IsDate() =0 to see if you have any invalid dates to worry about – Sparky Jan 13 '11 at 02:46

score 21 · Answer 2 · edited Aug 04 '20 at 16:13

21

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.

edited Aug 04 '20 at 16:13

VoronoiPotato

3,113
20
30

answered Jan 13 '11 at 02:13

John Petrak

2,898
20
31

score 8 · Answer 3 · answered Jan 13 '11 at 02:15

8

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

order by CONVERT(datetime, EventDate ) desc

answered Jan 13 '11 at 02:15

Jason Jong

4,310
2
25
33

score 7 · Answer 4 · answered Jan 13 '11 at 02:23

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.

score 4 · Answer 5 · answered Jan 13 '11 at 02:14

4

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

answered Jan 13 '11 at 02:14

Robokop

906
1
5
12

whoever set this up had the EventDate set as a varchar for some reason :/ – user570457 Jan 13 '11 at 02:34

score 4 · Answer 6 · answered Jan 13 '11 at 02:14

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

score 0 · Answer 7 · answered Apr 15 '21 at 05:16

0

Try this

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

answered Apr 15 '21 at 05:16

Bhargav Variya

725
1
10
18

Order by descending date - month, day and year

7 Answers7

Linked