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

User · Answer

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.

User · Answer

https   www postgresql org docs 8 4 functions-formatting html SELECT to char date field   DD MM YYYY   FROM table

User · Answer

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  gt  SELECT to char DATE  2014-04-01    DD MM YYYY      to char    ------------  01 04 2014  1 row

User · Answer

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  quot Formated Date quot

[string] PostgreSQL : cast string to date DD/MM/YYYY

Examples related to string

Examples related to postgresql

Examples related to date

Examples related to casting