You seriously need to change your DB to achieve this in any meaningful way: storing date based data in strings is always a poor idea and string comparisons are always based on the first different pair of characters - subsequent ones aren't even looked at. So the order would be:
apr2022
apr2023
aug2022
aug2023
dec2022
dec2023
feb2022
feb2023
jan2022
jul2022
jul2023
jun2022
jun2023
mar2022
mar2023
may2022
may2023
nov2022
nov2023
oct2022
oct2023
sep2022
sep2023
Which is pretty much useless.
While it's possible to do it, it takes a lot of effort - and luck: one French or German month name will mess your code right up for example.
The CAST function will let you do it:
CAST('oct2023' AS DATE)Will give you 2023-10-01 but it'll add a lot of processing and complexity to your queries - all of them - and still won't give you the ordering you need within each month because your data doesn't contain it: all dates will be the first of the month. And it'll hit performance as well as your number of rows grows.
At the very least, add a timestamp column (DATETIME or DATETIME2) to your DB and default it to the insert time (in UTC by preference) and use that instead of the string based junk you currently have.
Submit an article or tip