how to write a query for this scenario?

Question

I had a query like this,

SELECT 
    CONVERT(VARCHAR(3), DATENAME(MONTH, date)) + '-' + 
                        RIGHT(DATENAME(YEAR, date), 2) AS date 
FROM
    tblSample 
GROUP BY
    CONVERT(VARCHAR(3), DATENAME(MONTH, date)) + '-' + 
                        RIGHT(DATENAME(YEAR, date), 2) AS date 
ORDER BY
    CONVERT(VARCHAR(3), DATENAME(MONTH, date)) + '-' + 
                        RIGHT(DATENAME(YEAR, date), 2) AS date 

I had data like this:

Apr-17
jan-16
jan-17
mar-17

I have a requirement that the output should order by year part which means all the '17' (year parts) data should be shown. Like that we have to use order by for year part only how can I do that?

Answer 1

select distinct CONVERT(VARCHAR(3),DATENAME(MONTH,date))+'-'+right(DATENAME(‌​
YEAR,date),2) as date,YEAR([date]) as year,MONTH([date]) as month from 
tblSample order by YEAR([date]) desc,MONTH([date])

Answer 2

You have to just change your order by query as below, you include both month name and year in order by instead of just select year in order by clause

ORDER BY  RIGHT(DATENAME(YEAR, date), 2) AS date 

If you wants to apply order on first year and then month then write query as below:

ORDER BY RIGHT(DATENAME(YEAR, [date]), 2) DESC, CONVERT(VARCHAR(3), DATENAME(MONTH, [date]))

I have tried below data:

DECLARE @tblA AS TABLE(
    [date] date
)

INSERT INTO @tblA VALUES('01-Apr-2017')
INSERT INTO @tblA VALUES('01-Jan-2016')
INSERT INTO @tblA VALUES('01-Jan-2017')
INSERT INTO @tblA VALUES('01-Mar-2017')

SELECT 
    CONVERT(VARCHAR(3), DATENAME(MONTH, [date])) + '-' + 
                        RIGHT(DATENAME(YEAR, [date]), 2) AS date
FROM @tblA ORDER BY YEAR([date]) DESC, MONTH([date]) 

Output:

Answer 3

select CONVERT(VARCHAR(3),DATENAME(MONTH,date))+'-
'+right(DATENAME(YEAR,date),2) as date from 
tblSample 
order by YEAR([date]),MONTH([date])