Menu
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.
255
Here's how to sort unsorted dates: Drag down the column to select the dates you want to sort. Click Home tab > arrow under Sort & Filter, and then click Sort Oldest to Newest, or Sort Newest to Oldest.
A standard order is often called ascending (corresponding to the fact that the standard order of numbers is ascending, i.e. A to Z, 0 to 9), the reverse order descending (Z to A, 9 to 0). For dates and times, ascending means that earlier values precede later ones e.g. 1/1/2000 will sort ahead of 1/1/2001.
Thus, a descending ordering puts the most recent dates first. Because sorting by dates is much more common than by name, ordering is descending by default.
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
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
