How to sort month names in month order instead of alphabetical order is a common problem and has been discussed on multiple forums. But what encourages me to write this post is, I found new methods to do it.
Let me create a sample to explain multiple methods to achieve it.
USE tempdb GO CREATE TABLE tbl_Sample ( [ID] INT, [Date] DATETIME ) GO INSERT INTO tbl_Sample VALUES (1,'2013-04-04') INSERT INTO tbl_Sample VALUES (2,'2013-07-07') INSERT INTO tbl_Sample VALUES (3,'2013-10-10') INSERT INTO tbl_Sample VALUES (4,'2013-01-01') INSERT INTO tbl_Sample VALUES (5,'2013-02-02') INSERT INTO tbl_Sample VALUES (6,'2013-03-03') INSERT INTO tbl_Sample VALUES (7,'2013-05-05') INSERT INTO tbl_Sample VALUES (8,'2013-06-06') INSERT INTO tbl_Sample VALUES (9,'2013-08-08') INSERT INTO tbl_Sample VALUES (10,'2013-09-09') INSERT INTO tbl_Sample VALUES (11,'2013-11-11') INSERT INTO tbl_Sample VALUES (12,'2013-12-12') GO
Method 1 :
This is the simplest method, wherein you just need to sort on the basis of column [date] instead of DATENAME(month,Date).
Given below is the script.
--This script is compatible with SQL Server 2005 and above USE tempdb GO SELECT DATENAME(month,Date) AS [Month Name] , [Date] FROM tbl_Sample ORDER BY [Date] --OUTPUT
Method 2 :
In this method, you need to get the month number using Month function and sort it on month number.
Given below is the script.
--This script is compatible with SQL Server 2005 and above USE tempdb GO SELECT DATENAME(month,Date) AS [Month Name] , [Date] FROM tbl_Sample ORDER BY Month(Date) --OUTPUT
Method 3 :
In this method, you need to get the month number using DatePart function and sort it on month number.
Given below is the script.
--This script is compatible with SQL Server 2005 and above USE tempdb GO SELECT DATENAME(month,Date) AS [Month Name] , [Date] FROM tbl_Sample ORDER BY DATEPART(m,Date) --OUTPUT
Method 4 :
In this method, you need to get the month number using Format function and sort it on month number.
Given below is the script.
--This script is compatible with SQL Server 2012 and above USE tempdb GO SELECT DATENAME(month,Date) AS [Month Name] , [Date] FROM tbl_Sample Order By FORMAT([Date],'MM') --OUTPUT
[…] my earlier article, I had discussed about how to sort month names in month order instead of alphabetical order . In that article, we had given the date and we had to convert into month and sort it. But, in […]
[…] SQL SERVER – How to sort month names in month order … – How to sort month names in month order instead of alphabetical order is a common problem and has been discussed on multiple forums. But what encourages me to write … […]
I am getting this error below when I use the above script even though I have the column name in the select statement:
Msg 8127, Level 16, State 1, Line 7
Column “Table.Datecolumn” is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Here is my original query before I tried your suggestion
SELECT DISTINCT
DATENAME(Month,Datecolumn) Date_By_Month
, COUNT(ID) NumberofIDs
FROM Table
GROUP BY DATENAME(Month,DateColumn)
ORDER BY DATENAME(Month,DateColumn)