How do I alter the date format in Postgres?

Question

I'm getting the following error message

ERROR: date/time field value out of range: "13/01/2010" HINT: Perhaps you need a different "datestyle" setting.

I want to get my date in the format DD/MM/YYYY

Answer 1

SHOW datestyle;   DateStyle  -----------  ISO, MDY (1 row)  INSERT INTO container VALUES ('13/01/2010'); ERROR:  date/time field value out of range: "13/01/2010" HINT:  Perhaps you need a different "datestyle" setting.  SET datestyle = "ISO, DMY"; SET  INSERT INTO container VALUES ('13/01/2010'); INSERT 0 1  SET datestyle = default; SET 

http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE

DateStyle - Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).

Of course it's best to use unambiguous input format (ISO 8601), but there is no problem to adjust it as you need.

Answer 2

You could set the date style to European dd/mm/yyyy:

SET DateStyle TO European; 

I'd advise against this though. I generally try to convert between formats, and keep ISO formatted dates in the data source. After all, it's only a matter of representation, not a matter of different data.