31

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

deltanovember
  • 42,611
  • 64
  • 162
  • 244

3 Answers3

54
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.

Grzegorz Szpetkowski
  • 36,988
  • 6
  • 90
  • 137
9

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.

xlm
  • 6,854
  • 14
  • 53
  • 55
Berry Langerak
  • 18,561
  • 4
  • 45
  • 58
  • 1
    You should keep formatted dates in the database at all. Store them as DATE fields, and parse them into whatever format you need when you need them. – Paul Tomblin May 25 '11 at 11:21
  • My specific case - a European source of data using their date style which caused conflicts in production since ISO was the server default. Parsing the dates into objects yourself is the best way to go. – Patrick Mutuku Nov 19 '20 at 10:37
1

Edit:

When using this COPY, the valid input format is defined by the server configuration and can either be changed for the current session using the SET command as described by Berry or by adjusting the server configuration.

DateStyle description in the manual:
http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE

The following is not valid for the real situation, but I'm keeping it for reference anyway

When using date (or timestamp) literals always specify a format mask to convert them. Otherwise your statements aren't portable and won't necessarily run on every installation.

The ANSI SQL standard for date literals is like this:

UPDATE some_table
   SET date_column = DATE '2011-05-25'
WHERE pk_column = 42;

If you cannot change the literal format, you need to apply the to_date() function

UPDATE some_table
   SET date_column = to_date('13/01/2010', 'dd/mm/yyyy')
WHERE pk_column = 42;

If this is not what you are doing you should show us the full SQL statement that generated the error.