SQL 按日期降序排列 - 月、日和年
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4676139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order by descending date - month, day and year
提问by user570457
This seems stupid but, I simply need a list of dates to be ordered with the most recent date at top. Using order by DESCdoesn't seem to be working the way I want it to.
这看起来很愚蠢，但是，我只需要一个日期列表，将最近的日期放在顶部。使用order by DESC似乎不像我想要的那样工作。
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.
等等。
回答by Conrad Frix
I'm guessing EventDateis a char or varchar and not a date otherwise your order by clause would be fine.
我猜EventDate是 char 或 varchar 而不是日期，否则您的 order by 子句就可以了。
You can use CONVERT to change the values to a date and sort by that
您可以使用 CONVERT 将值更改为日期并按日期排序
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.
问题在于，正如 Sparky 在评论中指出的那样，如果 EventDate 的值无法转换为查询将不会执行的日期。
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
要排除坏行，只需添加 WHERE IsDate(EventDate) = 1
To let let the bad dates go to the bottom you need to use CASE
为了让糟糕的日期走到底部，你需要使用 CASE
e.g.
例如
ORDER BY
CASE
WHEN IsDate(EventDate) = 1 THEN CONVERT(DateTime, EventDate,101)
ELSE null
END DESC
回答by John Petrak
try ORDER BY MONTH(Date),DAY(DATE)
尝试 ORDER BY MONTH(Date),DAY(DATE)
UPDATE BY ANONYMUS USER:
匿名用户更新：
Try this:
尝试这个：
ORDER BY YEAR(Date) DESC, MONTH(Date) DESC, DAY(DATE) DESC
ORDER BY YEAR(Date) DESC, MONTH(Date) DESC, DAY(DATE) DESC
Worked perfectly on a JET DB.
在JET DB上完美运行。
回答by Jason Jong
You have the field in a string, so you'll need to convert it to datetime
您在字符串中有该字段，因此您需要将其转换为日期时间
order by CONVERT(datetime, EventDate ) desc
回答by Martin Smith
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. dateif SQL Server 2008).
假设您有权更改架构，IMO 对此问题唯一可接受的答案是将基本数据类型更改为更合适的类型（例如，date如果是 SQL Server 2008）。
Storing dates as mm/dd/yyyystrings is space inefficient, difficult to validate correctly and makes sorting and date calculations needlessly painful.
将日期存储为mm/dd/yyyy字符串空间效率低下，难以正确验证，并使排序和日期计算不必要地痛苦。
回答by Robokop
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
字段的类型是什么EventDate，因为排序不正确，我假设您没有将它设置为某些日期/时间表示类型，而是一个字符串。然后美国写日期的方式很难排序
回答by Kurru
If you restructured your date format into YYYY/MM/DD then you can use this simple string ordering to achieve the formating you need.
如果您将日期格式重组为 YYYY/MM/DD，那么您可以使用这个简单的字符串排序来实现您需要的格式。
Alternatively, using the SUBSTR(store_name,start,length)command you should be able to restructure the sorting term into the above format
或者，使用SUBSTR(store_name,start,length)命令，您应该能够将排序项重组为上述格式
perhaps using the following
也许使用以下
SELECT *
FROM vw_view
ORDER BY SUBSTR(EventDate,6,4) + SUBSTR(EventDate, 0, 5) DESC
