date - How to convert 'dd-mm-yyyy' to 'dd-mmm-yyyy' in PostgreSQL?

Question

I want to convert data format 'DD-MM-YYYY' (e.g., '31-03-2020' ) to 'DD-MMM-YYYY' (e.g., '31-MAR-2020'). Can someone please help me out here?

Answer 1

You'll need to first convert the non-standard date (31-03-2020, which is actually a text or varchar type, in Postgres terms), to a DATE type with to_date(), then convert it to the format you're looking for (which again, is actually a text or varchar type):

postgres=# select to_char(to_date('31-03-2020','DD-MM-YYYY'), 'DD-Mon-YYYY');
   to_char   
-------------
 31-Mar-2020
(1 row)

Or to make it a little more human-readable:

postgres=# WITH dt AS (SELECT to_date('31-03-2020','DD-MM-YYYY') mydate)
SELECT to_char(dt.mydate, 'DD-Mon-YYYY') FROM dt;
   to_char   
-------------
 31-Mar-2020
(1 row)