Microsoft T/SQL, Stored procedure

Question

Sorting based on Year and then Month. I have one Column Period.

It has contins dates from Jan-2016 up to APR-2018.

I want to Sort based on Recent Year then Month also needs to be sorted.

Result should look like:

• Jan-2018
• Feb-2018
• Mar-2018
• Apr-2018
• Jan-2017
• Feb-2017 up to Dec-2017
• then for 2016: Jan-2016
• Feb-2016
• Mar-2016 up to Dec-2016

I did something like this, but it has an issue:

Cast(Year(cast(Q.Column Name as small date time)) as char(4)) + '-' + CASE WHEN Month(cast (Q.Column Name as small date time)) < 10 THEN '0' ELSE '' END  + cast(Month(cast (Q.Column Name as small date time)) as char(2)) 

Thank you in advance

Answer 1

You can use apply ORDER BY that separately orders against year and month using CAST and DATEPART:

declare @tmp table ([period] varchar(10))
insert into @tmp values 
('Jan-2016'), ('Feb-2016'), ('Mar-2016'), ('Apr-2016'), ('May-2016'), ('Jun-2016'), ('Jul-2016'), ('Aug-2016'), ('Sep-2016'), ('Oct-2016'), ('Nov-2016'), ('Dec-2016'), ('Jan-2017'), ('Feb-2017'), ('Mar-2017'), ('Apr-2017'), ('May-2017'), ('Jun-2017'), ('Jul-2017'), ('Aug-2017'), ('Sep-2017'), ('Oct-2017'), ('Nov-2017'), ('Dec-2017'), ('Jan-2018'), ('Feb-2018'), ('Mar-2018'), ('Apr-2018')

select  *
from @tmp 
order by DATEPART(year, cast( '1-' +  [period] as date)) desc, 
         DATEPART(month,cast( '1-' +  [period] as date)) asc

On the left side the original order, on the right side the results after the order by:

In your case:

select  *
from YOUR_TABLE_NAME 
order by DATEPART(year, cast( '1-' +  [period] as date)) desc, 
         DATEPART(month,cast( '1-' +  [period] as date)) asc