按月、日和年的降序排列

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.

这看起来很愚蠢，但我只是需要一个日期列表，其中最上面是最近的日期。使用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:

它给了我按月和日订的日期，但没有考虑年份。例如:

Needs to be more like:

需要更像:

and so on.

等等。

6 个解决方案

#1

I'm guessing EventDate is 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

要排除坏行，只需添加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

#2

Try:

试一试:

ORDER BY MONTH(Date),DAY(DATE)

EDIT (By anonymous user.)

编辑(通过匿名用户。)

I believe this post is done with, however this user above has hit on something that resolved my issue above all others.

我相信这篇文章已经完成了，但是上面的用户已经找到了解决我问题的方法。

If anyone is having trouble with the other solutions try a minor mod of the above code.

如果有人对其他解决方案有问题，可以尝试对上面的代码做一个小的修改。

ORDER BY YEAR(Date) DESC, MONTH(Date) DESC, DAY(DATE) DESC

This was my final approach and it worked perfectly on a JET DB.

这是我最后的方法，它在JET DB上运行得很好。

PS. I hope no one is actually using Date as their fieldname ;-)

我希望没有人实际使用日期作为他们的fieldname;-)

#3

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

字符串中有字段，因此需要将其转换为datetime

order by CONVERT(datetime, EventDate ) desc

#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

字段EventDate的类型是什么?由于顺序不正确，我假设您没有将它设置为表示类型的日期/时间，而是一个字符串。另外，美国人写日期的方式很难分类

#5

If you restructured your date format into YYYY/MM/DD then you can use this simple string ordering to achieve the formating you need.

如果您将日期格式调整为yyyyy /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

#6

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).

假设您有能力进行模式更改，那么IMO对这个问题的唯一可接受的答案就是将基本数据类型更改为更合适的类型(例如，如果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.

将日期存储为mm/dd/yyyy字符串的空间效率很低，难以正确验证，并且使排序和日期计算变得不必要地痛苦。

#1