Date sorting issue in SQL server.

Question

4.33/5 (2 votes)

See more:

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

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

Posted 14-Feb-18 21:22pm

Agarwal1984

Updated 15-Feb-18 6:35am

Add a Solution

2 solutions

Solution 2

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:

SQL

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
;

Posted 15-Feb-18 6:30am

Richard Deeming

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-02-14T21:29:00

Solution 1

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

Posted 14-Feb-18 21:29pm

OriginalGriff

Comments

Herman<T>.Instance 15-Feb-18 3:40am

that's it!

Date sorting issue in SQL server.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Date sorting issue in SQL server.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us