PostgreSQL : cast string to date DD/MM/YYYY

Question

I'm trying to cast a CHARACTER VARYING column to a DATE but I need a date format like this : DD/MM/YYYY. I use the following SQL query :

ALTER TABLE test  ALTER COLUMN date TYPE DATE using to_date(date, 'DD/MM/YYYY'); 

The result is a date like this : YYYY-MM-DD.

How can I get the DD/MM/YYYYformat ?

Thanks a lot in advance !

Thomas

Answer 1

A DATE column does not have a format. You cannot specify a format for it.

You can use DateStyle to control how PostgreSQL emits dates, but it's global and a bit limited.

Instead, you should use to_char to format the date when you query it, or format it in the client application. Like:

SELECT to_char("date", 'DD/MM/YYYY') FROM mytable; 

e.g.

regress=> SELECT to_char(DATE '2014-04-01', 'DD/MM/YYYY');   to_char    ------------  01/04/2014 (1 row)