Parameter · Client Connection Defaults / Locale and Formatting
DateStyle
Sets the display format for date and time values. Also controls interpretation of ambiguous date inputs.
- Default:
ISO, MDY - Type: string
- Applies: Session (any user)
In plain words
DateStyle does two jobs. Its first part picks the output format for dates and timestamps (ISO 2025-03-07, SQL 03/07/2025, German 07.03.2025 …). Its second part (MDY, DMY or YMD) decides how an ambiguous input like 03/07/2025 is read: 7 March or 3 July?
The stored value never changes; only how it is shown and how text is parsed.
For DBAs and operators
The value is a list of up to two keywords: an output style (ISO, Postgres, SQL, German) and a field order (MDY, DMY, YMD). Either half may be set alone; the other is kept. German implies DMY. The order also matters for output in the SQL and Postgres styles (SQL, DMY prints 07/03/2025).
The built-in default is ISO, MDY, but initdb writes a value matching the lc_time locale, so European clusters often run ISO, DMY. Clients can also override it per connection (libpq PGDATESTYLE). DateStyle is reported to the client on every change (ParameterStatus).
Support pitfalls:
- An application that sends dates as
dd/mm/yyyytext works on one server and silently swaps day and month on another. Unambiguous ISO input (2025-03-07) is always parsed the same way. - With
YMD, an input like03/07/2025fails: ERROR: date/time field value out of range: "03/07/2025" withHINT: Perhaps you need a different "DateStyle" setting. - pgJDBC requires the ISO output style and refuses to continue if a session changes it.
For formatting in reports use to_char() instead of relying on DateStyle. Month and day names in to_char come from lc_time, not from DateStyle.
How to change it
Any user can change it for their session with SET.