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

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

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

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

[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