Q:

Ordering of month/year pairs in T-SQL query

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order.
ALTER procedure [dbo].[audioblog_getarchivedates]  
as  
begin  
select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate 
from audio_blog a 
group by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) 
order by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) desc
end

Results will come like this:

March 2010
  January 2010
  February 2010 

But that is not in a order (desc).

A:

Jeff Smith has written an excellent article on methods how to group and order by Year-Month pairs. I like especially his approach of rounding the date to the first day of the month and grouping by that value:
GROUP BY dateadd(month, datediff(month, 0, SomeDate),0)

It's faster because it doesn't require string concatenation.
You can convert this value to a year-month combination later on in the query by executing YEAR and MONTH on it for a user-friendly representation.

A:

Not the best solution but should work until you figure it out.
ALTER PROCEDURE [dbo].[Audioblog_getarchivedates] 
AS 
  BEGIN 
    SELECT DISTINCT Datename(MONTH,a.DATE) + ' ' + Datename(YEAR,a.DATE) AS archivedate,
    Cast(Datename(MONTH,a.DATE) + ' ' + Datename(YEAR,a.DATE) AS DATETIME) AS tmp
    FROM audio_blog a 
    ORDER BY tmp DESC 
  END

