CODEWITHSUNDEEP
sqlsql-servert-sql
AlisonGrey
AlisonGrey

Reputation: 507

SQL: Order Month_year in date order

I have a data which is

Monthyear (varchar)       Value
Jan 2019                   55
Feb 2019                   66
Jan 2020                   78
Feb 2020                   45

when i select the above statement as 

select * from datatable order by Monthyear

the result is actually giving me 

 Monthyear (varchar)       Value
    Feb 2019                   66
    Feb 2020                   45
    Jan 2019                   55
    Jan 2020                   78

But I want it in the month year order. How do I resolve this?

Upvotes: 2

Views: 908

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You should store the "month-year" either as a date (first date of the month) or using a string that is sortable ("2019-01").

However, SQL Server is pretty good at converting strings to dates, so you can use:

order by convert(date, MonthYear)

This will convert by the date that you want.

Actually, you might want to add this to the table. Say:

alter table datatable add yyyymm as (convert(date, MonthYear));

You can even persist this and add an index, if you have a lot of data in the table.

Upvotes: 3

DxTx
DxTx

Reputation: 3347

You can also use CAST to do this.

SELECT *
FROM   datatable
ORDER  BY Cast(monthyear AS DATE)

Result

+-----------+-------+
| Monthyear | Value |
+-----------+-------+
| Jan 2019  |    55 |
| Feb 2019  |    66 |
| Jan 2020  |    78 |
| Feb 2020  |    45 |
+-----------+-------+

Upvotes: 1

Related Questions