CODEWITHSUNDEEP
sql-server
Cremlic
Cremlic

Reputation: 88

SQL Server Query : Month sort

I am trying to sort month But this is the result.

Query:

SELECT DISTINCT Month FROM ClientsDataAging ORDER BY Month ASC

Result

enter image description here

It seems it was sorted Alphabetically.

I want the result that sorts monthly like Jan, Feb, March etc. etc.

I found this answer : https://stackoverflow.com/a/17271676/9651031 but It seems it is differ from my case so, any help will be appreciated. TIA!

Upvotes: 2

Views: 165

Answers (3)

par
par

Reputation: 1121

It will require to convert name to month number in ORDER BY. something like : 

SELECT DISTINCT Month FROM ClientsDataAging ORDER BY 
DATEPART(MM,Month + ' 01 2018')   ASC

(.. i have not tested it because posting this from phone)

Edit for ORDER BY items must appear in..:

;with t as 
(select distinct [Month], DATEPART(MM, [Month] + ' 01 2018') MonthNum from ClientsDataAging)
select [Month] From t ORDER BY MonthNum ASC

...You should have removed DISTINCT if you don't need it as done in accepted answer.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Given that you have your months stored as string data, the output you currently see is correct, because those dates are sorting as text, and not dates. One option would be to use CONVERT with format mask 107:

SELECT Month
FROM ClientsDataAging
ORDER BY CONVERT(datetime, SUBSTRING(Month, 1, 3) + ' 01 18');

enter image description here

The trick here is that, for example, for the month 'January', we build the string 'Jan 01 18'. This is the format of 'Mon dd, yy' which CONVERT expects for mask 107. So we convert your text month into a bona fide date on the first of that month. I arbitrarily use 2018 as the year, but this should not matter, since you never mentioned any year data.

Here is a demo showing that all the text months convert properly.

Demo

Upvotes: 1

Rajat Jaiswal
Rajat Jaiswal

Reputation: 643

SELECT DISTINCT Month FROM ClientsDataAging ORDER BY MONTH('01-' + [Month] + '-2018') ASC

Try above script.

Upvotes: 0

Related Questions