How to sort by month in SQL Server

By Benjamin Perkins · ·

This is a older blog which I am migrating, you can see how the older website looked like here.

You can see on the Blog.Archive() navigation bar on the right of this page that I present the Year, Month and number of blogs which I have written each month. Everything worked fine from October 2010 until February 2011 with my query. However, in March, all of a sudden the months were no longer in order by date, seemed like they were being sorted by order.

Here is the query I used to sort the and retrieve the Blog.Archive() list from my database. It uses a CASE statement to sort the data by month.

"SELECT DATENAME(MONTH, CREATIONDATE) AS MONTH, " +
"DATENAME(YEAR, CREATIONDATE) AS YEAR, COUNT(*) "
"AS BLOGS FROM POSTS " +
"WHERE DATENAME(YEAR,CREATIONDATE)=DATENAME(YEAR,GetDate()) " +
"GROUP BY DATENAME(MONTH, CREATIONDATE), " +
"DATENAME(YEAR, CREATIONDATE) " +
"ORDER BY CASE LEFT(DATENAME(MONTH, CREATIONDATE), 3) " +
"WHEN 'JAN' THEN '01' " +
"WHEN 'FEB' THEN '02' " +
"WHEN 'MAR' THEN '03' " +
"WHEN 'APR' THEN '04' " +
"WHEN 'MAY' THEN '05' " +
"WHEN 'JUN' THEN '06' " +
"WHEN 'JUL' THEN '07' " +
"WHEN 'AUG' THEN '08' " +
"WHEN 'SEP' THEN '09' " +
"WHEN 'OCT' THEN '10' " +
"WHEN 'NOV' THEN '11' " +
"WHEN 'DEC' THEN '12' " +
"ELSE '' END DESC";