How to sort month in a table when retrieving using the sql query?

Question

I have a query which gets month name from a table. But this column isn't a datetime data type, It's a varchar column. How can I sort it according to the month name in ascending order?
This is the output I get at the moment.

August
November
October
September

This is my query

select distinct(payemnt_month) as month from payement_details

do you have any other corresponding date or datetime column in the table ? — Squirrel, Nov 03 '16 at 06:12

Tharunkumar Reddy · Accepted Answer · 2016-11-03T06:51:40.497

3

Use below one i have appended your month with day and year. Then i am extracting month number

select * from payment_details order by DATEPART(MM,payemnt_month+'01'+'00')

Update If possible update your query like below.

SELECT * FROM 
(
SELECT DISTINCT month,
                Datepart(MM, payemnt_month+ '01' + '00') MONTHNO
FROM   payment_details )A
ORDER  BY MONTHNO

Or Like below if you don't have any issue to keep month no

   SELECT DISTINCT month,
                        Datepart(MM, payemnt_month+ '01' + '00') MONTHNO
        FROM   payment_details
order by month,Datepart(MM, payemnt_month+ '01' + '00')

edited Nov 03 '16 at 06:51

answered Nov 03 '16 at 06:15

Tharunkumar Reddy

2,773
18
32

I tried to change the query accoring to mine. But it gives me the following error. Msg 145, Level 15, State 1, Line 4 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. – Mike Nov 03 '16 at 06:32
What and all columns you have specified after distinct? Can you post your entire query? – Tharunkumar Reddy Nov 03 '16 at 06:33
This is the entire query – Mike Nov 03 '16 at 06:38
@Mike Updated post check now. – Tharunkumar Reddy Nov 03 '16 at 06:46

score 2 · Answer 2 · answered Nov 03 '16 at 06:20

ORDER BY CASE WHEN payment_month='August' THEN 8
              WHEN payment_month='November' THEN 11
              WHEN payment_month='October' THEN 10
              WHEN payment_month='September' THEN 9 END

Frame your ORDER BY clause as above and add remaining months into it as required.

score 0 · Answer 3 · answered Nov 03 '16 at 06:15

0

Just put order by clause as:

select distinct(payemnt_month) as month from payement_details order by payemnt_month

answered Nov 03 '16 at 06:15

Rahul Maurya

187
5

How to sort month in a table when retrieving using the sql query?

3 Answers3