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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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!