I'm trying to sort using the months name, when I have it in numbers everything is working, but when I try to make a select like this
SELECT DISTINCT CONVERT(nvarchar(50), DATENAME(m, date) + ', ' + DATENAME(yyyy,date)) as date From MyTable In my output I have something like this
August, 2015 July, 2015 September, 2015 How to sort by month, not by alphabetic order?
I tried to add order by date but without any result. And the output format should be exactly like this.
Just embed the query into subquery and select month number along with your string:
DECLARE @t TABLE ( [date] DATE ) INSERT INTO @t VALUES ( '20150701' ), ( '20150701' ), ( '20150801' ), ( '20150901' ) SELECT [date] FROM ( SELECT DISTINCT CONVERT(NVARCHAR(50), DATENAME(m, [date]) + ', ' + DATENAME(yyyy, [date])) AS [date] , YEAR([date]) AS y , MONTH([date]) AS m FROM @t ) t ORDER BY t.y, t.m Output:
date July, 2015 August, 2015 September, 2015 
You should set a order criteria that you must have in the selected list. Try something like this:
SELECT DISTINCT CONVERT(nvarchar(50), DATENAME(m, date) + ', ' + DATENAME(yyyy,date)) as date, year(date) * 100 + month(date) as ordercrit From MyTable order by ordercrit The ordercrit should also contain the year if you want it to be considered in the ordering, like,
January, 2014 February, 2014 January, 2015 May, 2015 January, 2016 
Another method
DECLARE @t TABLE ( [date] DATE ) INSERT INTO @t VALUES ( '20150701' ), ( '20150701' ), ( '20150801' ), ( '20150901' ) SELECT DATE FROM ( SELECT DISTINCT CONVERT(NVARCHAR(50), DATENAME(M, DATE) + ', ' + DATENAME(YYYY,DATE)) AS DATE FROM @T ) AS T ORDER BY CAST(DATE AS DATETIME) 
order byto sort in SQL, but you shouldn't sort by your converted value but likely the original valueORDER BY items must appear in the select list if SELECT DISTINCT is specified.