Problem with DISTINCT, SELECT and SORT in TSQL

Question

Hi there,

maybe anyone can help me out with my SELECT statement on MS SQL Server. I am not very skilled in (T)SQL.

I have a table called RECORDS and with a DATETIME column called [BEGIN]. Now I would like to get some nvarchars which look like this "December 08, January 09, February 09"..

I came up with the following myself.

SELECT DISTINCT DATENAME(MONTH, [BEGIN]) + ' ' + SUBSTRING(DATENAME(YEAR, [BEGIN]),3,4) 
FROM RECORDS

However this is unsorted I would like to have the result set ordered from first to last.

February 09
January 09
December 08

Anyone?

Answer 1

SELECT  DATENAME(MONTH, DATEADD(month, b_month - 1, 0)) + ' ' + SUBSTRING(CAST(b_year AS VARCHAR), 3, 4)
FROM    (
        SELECT  DISTINCT YEAR([BEGIN]) AS b_year, MONTH([BEGIN]) AS b_month
        FROM    RECORDS
        ) q
ORDER BY
        b_year, b_month

Answer 2

what about

with tbldate AS 
(
select distinct DATENAME(MONTH, date) + ' ' + SUBSTRING(DATENAME(YEAR, date),3,4)  as date
from dbo.tbldate  
)
select * from tbldate
order by date desc

Answer 3

Add ORDER BY [BEGIN] DESC

Answer 4

SELECT DISTINCT DATENAME(MONTH, [BEGIN]) + ' ' + SUBSTRING(DATENAME(YEAR, [BEGIN]),3,4) FROM RECORDS
ORDER BY 1 DESC

Answer 5

select  datename(month, yyyymm) 
    + ' ' 
    + right(convert(varchar(10), yyyymm), 2)
from
(
    select  dateadd(month, datediff(month, 0, [BEGIN]), 0) as yyyymm
    from    yourtable
    group by dateadd(month, datediff(month, 0, [BEGIN]), 0)
) a
order by yyyymm desc