CODEWITHSUNDEEP
postgresqldatedatetimepsqldate-conversion
Rahul Kumar
Rahul Kumar

Reputation: 149

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

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?

Upvotes: 1

Views: 4763

Answers (2)

Giorgos Tsakonas
Giorgos Tsakonas

Reputation: 6105

In case you need to convert the returned date of a select statement to a specific format you may use the following:

select to_char(DATE (*date_you_want_to_select*)::date, 'DD-Mon-YYYY') as "Formated Date"

In case you need to convert a specific date to a specific format you may use the following:

select to_char(DATE '2020-03-31', 'DD-Mon-YYYY') as "Formated Date"

Upvotes: 0

richyen
richyen

Reputation: 9968

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)

Upvotes: 2

Related Questions