SQL Server中的日期排序问题。 [英] Date sorting issue in SQL server.

问题描述

您好，



我想显示日期，根据最新更新但我的日期排序错误。



我需要结果： -

Hello,

I want to display date, according to last updated but my date sorting is wrong.

I need to Result:-

Months         Request
February 18	     4
January 18	     60
December 17	     32
November 17	     7

但结果显示： -

but result displayed:-

Months          Request
November 17	     7
January 18	     60
February 18	     4
December 17	     32

如何以正确的格式显示数据（降序月份）？

请帮帮我。



谢谢提前。



Ankit Agarwal

软件工程师



什么我试过了：

how to display data in correct format (descending month year)?
Please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

What I have tried:

select  FORMAT(CreatedDateTime,'MMMM yy') as Months, count(*) as Request from LRequests 
group by FORMAT(CreatedDateTime,'MMMM yy')
order by FORMAT(CreatedDateTime,'MMMM yy') DESC

推荐答案

您正在将日期排序为字符串，因为这是您将它们转换为的内容。字符串总是按字母顺序排序，而不是按时间顺序排序。

如果您想按月和月份订购，请不要将字符串版本用于您的ORDER BY子句：

You are sorting dates as strings, because that is what you have converted them to. Strings always sort in alphabetical order, not chronological.
If you want to order by year then month, don't use the string version for your ORDER BY clause:

ORDER BY CreatedDateTime DESC

正如Griff所说，您需要按日期值排序，而不是格式化日期。但是，如果您按格式化日期进行分组，则无法执行此操作。



尝试在月初进行分组，然后再应用格式。这样的事情应该有效：

As Griff said, you need to sort by the date value, not the formatted date. But you can't do that if you're grouping by the formatted date.

Try grouping by the start of the month, and applying the formatting later. Something like this should work:

WITH cte As
(
    SELECT
        DateFromParts(Year(CreatedDateTime), Month(CreatedDateTime), 1) As CreatedMonth,
        Count(*) As Request
    FROM
        LRequests
    GROUP BY
        DateFromParts(Year(CreatedDateTime), Month(CreatedDateTime), 1)
)
SELECT
    Format(CreatedMonth, 'MMMM yy') As Months,
    Request
FROM
    cte
ORDER BY
    CreatedMonth
;

这篇关于SQL Server中的日期排序问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！