| |
|
How to sort by month in SQL Server
|
| |
|
|
| |
| 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 retireive 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";
|
| |
|
|
| |
|
|
| |
|
|
| |
| |
| Posts: 113 |
| Comments:
86 |
| Fundamentals:
16 |
| |
 |
| |
| |
|
| |
 |
| |
 |
| |
 |
| |
|
| 2011 December (2) |
| 2011 November (6) |
| 2011 October (7) |
| 2011 September (7) |
| 2011 August (9) |
| 2011 July (9) |
| 2011 June (8) |
| 2011 May (9) |
| 2011 April (7) |
| 2011 March (9) |
| 2011 February (8) |
| 2011 January (8) |
| 2010 December (7) |
| 2010 November (8) |
| 2010 October (4) |
| |
| |
|
| |
|
|
| |
| |
|
The sample code on this website is provided to illustrate a concept and should not be used in
applications or Web sites without proper professional consultation, as it may not illustrate
the safest coding practices. I assume no liability for incidental or consequential damages
should the sample code be used for purposes other than as intended.
|
| |
|
| | | |
Newsletter | 
RSS Feeds
