CODEWITHSUNDEEP
sqlpostgresql
Jayadevan
Jayadevan

Reputation: 1342

PostgreSQL display month in correct order

I want to display data from a table sorted on the month in 'Jan', 'Feb' format. If I use to_char function, the sorting won't be correct. How can this be done? Sample data and query I am trying.

SELECT * FROM tb;

     dt     
------------
 2014-12-12    
 2014-01-01


SELECT to_char(dt,'Mon') FROM tb ORDER BY to_char(dt,'Mon');

 to_char 
---------
 Dec   
 Jan

Upvotes: 1

Views: 2431

Answers (2)

Beaordepe
Beaordepe

Reputation: 1

I had the same problem and I couldn't order by dt because I had an error

ORDER BY expressions must appear in select list.

I did the following:

SELECT mon
FROM (SELECT DISTINCT DATE_PART('MONTH', dt) AS m, TO_CHAR(dt,'Mon') AS mon FROM tb) AS mname
ORDER BY m;

I used DISTINCT to avoid repeated months.

Upvotes: 0

SMA
SMA

Reputation: 37023

You need to order by date and display month as per the date. Try something like:

SELECT TO_CHAR(dt,'Mon') 
FROM tb 
ORDER BY dt

Upvotes: 2

Related Questions