PostgreSQL : cast string to date DD/MM/YYYY

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/YYYY format ?

Thanks a lot in advance !

Thomas

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)

The documentation says

> The output format of the date/time types can be set to one of the four > styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date > format), or German. The default is the ISO format.

So this particular format can be controlled with postgres date time output, eg:

t=# select now();
              now
-------------------------------
 2017-11-29 09:15:25.348342+00
(1 row)

t=# set datestyle to DMY, SQL;
SET
t=# select now();
              now
-------------------------------
 29/11/2017 09:15:31.28477 UTC
(1 row)

t=# select now()::date;
    now
------------
 29/11/2017
(1 row)

Mind that as @Craig mentioned in his answer, changing datestyle will also (and in first turn) change the way postgres parses date.

https://www.postgresql.org/docs/8.4/functions-formatting.html

SELECT to_char(date_field, 'DD/MM/YYYY')
FROM table

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/MM/YYYY') as "Formated Date"

Depends on which type you require as output, but here are 2 quick examples based on an intervals:

• SELECT (now() - interval '15 DAY')::date AS order_date -> 2021-07-29
• SELECT to_char(now() - interval '15 DAY', 'YYYY-MM-DD') -> 2021-07-29

Let's say your date column is order_date:

SELECT (
    RIGHT(order_date, 4) 
    || '-' 
    || SUBSTRING(order_date, 4, 2) 
    || '-' 
    || LEFT(order_date, 2)
    )::DATE
FROM test

OR

SELECT CAST(
    RIGHT(order_date, 4) 
    || '-' 
    || SUBSTRING(order_date, 4, 2) 
    || '-' 
    || LEFT(order_date, 2)
    AS DATE )
FROM test