按日期降序排列 - 月、日和年 [英] Order by descending date - month, day and year

问题描述

这看起来很愚蠢，但是，我只需要一个要排序的日期列表，最近的日期在顶部.使用 order by DESC 似乎没有按照我想要的方式工作.

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

需要更像:

12/31/2009

12/30/2009

12/29/2009

12/28/2009

等等.

推荐答案

我猜 EventDate 是 char 或 varchar 而不是日期，否则您的 order by 子句就可以了.

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

您可以使用 CONVERT 将值更改为日期并按日期排序

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

问题在于，正如 Sparky 在评论中指出的那样，如果 EventDate 的值无法转换为日期，则查询将不会执行.

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

要排除坏行，只需添加 WHERE IsDate(EventDate) = 1

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

要让糟糕的日期深入到底部，您需要使用 CASE

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

例如

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

这篇关于按日期降序排列 - 月、日和年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！