SQL Server Portal

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

Read Full Post »