Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL rejects a day/month/year date string as out of range

ErrorERROR: date/time field value out of range: "30/12/2014 23:58:04"

Short answer

PostgreSQL reads 30/12/2014 using the DateStyle setting. With the default MDY order, the 30 is treated as a month and rejected. Send ISO 8601 dates, parse text with to_timestamp and an explicit format, or set DateStyle to DMY for the session.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask the server how it reads day, month and year order for this session.

    SHOW DateStyle;
  2. 2Match what you see, then jump to its fix

    1. 1DateStyle returns ISO, MDY and the text starts with a day above 12DateStyle is MDY and the first number is not a monthFix
    2. 2Inserts from a form, CSV import or ORM fail, and only for days after the 12thThe application sends a locale-style string instead of ISO 8601Fix
    3. 3The same import works on one server and fails on anotherThe server or database default order differs from what the data usesFix

What does the error look like?

  • ERROR: date/time field value out of range: "30/12/2014 23:58:04"

Cause 1 of 3

DateStyle is MDY and the first number is not a month

1Check

With MDY the first field is the month, so 30/12/2014 fails while 12/30/2014 would pass. The built-in default is ISO, MDY.

SHOW DateStyle;
SELECT '12/30/2014'::date;

2Fix

Stop relying on the setting. Convert the text with an explicit format, so the order is part of the query.

SELECT to_timestamp('30/12/2014 23:58:04', 'DD/MM/YYYY HH24:MI:SS');

3Verify

The query returns 2014-12-30 23:58:04 in the session time zone.

SELECT to_timestamp('30/12/2014 23:58:04', 'DD/MM/YYYY HH24:MI:SS')::date;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The application sends a locale-style string instead of ISO 8601

1Check

The manual lists 1999-01-08 as the recommended input: it means the same in any DateStyle. Day-first strings only work when the order matches the session.

SELECT '2014-12-30 23:58:04'::timestamp;

2Fix

Format dates as YYYY-MM-DD HH:MI:SS in the application, or pass date objects through a parameterised query, so no text parsing is needed.

INSERT INTO events (happened_at) VALUES ('2014-12-30 23:58:04');

3Verify

Insert the row and read it back.

SELECT happened_at FROM events ORDER BY happened_at DESC LIMIT 1;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The server or database default order differs from what the data uses

1Check

initdb writes a DateStyle into postgresql.conf that matches the lc_time locale of the cluster, so two servers can read the same text differently. A database can also carry its own default.

SHOW DateStyle;

2Fix

If all your input really is day-first, set the order for new sessions in that database, or for one session. This changes how every ambiguous date is read, so prefer the format fix when other code shares the database.

-- one session
SET DateStyle = 'ISO, DMY';
-- new sessions in one database
ALTER DATABASE mydb SET DateStyle TO 'ISO, DMY';

3Verify

Open a new session and cast the string.

SHOW DateStyle;
SELECT '30/12/2014 23:58:04'::timestamp;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Store and exchange dates as ISO 8601 text or as native date types.
  • Use to_timestamp or to_date with a format whenever you must parse a text date.
  • Keep DateStyle identical across development, staging and production, or avoid depending on it.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.