PostgreSQL rejects a day/month/year date string as out of range
ERROR: 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.
Fix it fast
1Find out which cause you have
Ask the server how it reads day, month and year order for this session.
SHOW DateStyle;2Match what you see, then jump to its fix
- 1
DateStyle returns ISO, MDY and the text starts with a day above 12DateStyle is MDY and the first number is not a monthFix - 2
Inserts 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
The same import works on one server and fails on anotherThe server or database default order differs from what the data usesFix
- 1
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
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix