PostgreSQL 22007 invalid_datetime_format: unparsed date text

Note: Follow the documented order. An error code identifies the affected system, but it does not prove by itself which part has failed.

1. 1

   Check the session DateStyle and TimeZone parameters with SHOW

   Tools required

   Who:

   Your developer or DBA inspecting the database session that returned 22007.

   How:

   If the current transaction is aborted, run ROLLBACK first, then execute SHOW DateStyle; and SHOW TimeZone; on the same connection pool or role used by the application.

   Result that matters:

   SHOW DateStyle displays the output style and input field ordering (for example ISO, MDY or ISO, DMY), and SHOW TimeZone shows the active session time zone.

   If SHOW DateStyle reports DMY or YMD while the application sends month-first strings like 1/18/1999, that confirms a DateStyle ordering mismatch triggered 22007.

   If DateStyle already matches the expected ordering, that rules out MDY/DMY configuration drift and points to a malformed literal, empty string, or unrecognized timezone token.

   Evidence to keep:

   Record the exact output of SHOW DateStyle; and SHOW TimeZone; along with the session role name.

   Use read-only SHOW commands to inspect session state; do not change global DateStyle in postgresql.conf without checking all existing clients.

   Evidence: PostgreSQL ROLLBACK · PostgreSQL 18 date/time types · PostgreSQL 18 SHOW

2. 2

   Inspect the ErrorResponse fields and test the literal with pg_input_error_info

   Tools required

   Who:

   Your application developer analyzing the rejected date or time string.

   How:

   Read ErrorResponse fields C (22007), M (Message), D (Detail), H (Hint), and P (Position), then run SELECT * FROM pg_input_error_info('raw_string', 'timestamp with time zone') (or date, time, or interval) using the redacted literal.

   Result that matters:

   pg_input_error_info tests whether the string is valid input for the specified date/time type and returns message, detail, hint, and sql_error_code without aborting the transaction.

   If pg_input_error_info returns sql_error_code 22007 when tested against the column's date/time type, that confirms the literal syntax is invalid for that parser.

   If the string is an empty string '' or contains words like 'CURRENT_TIMESTAMP' as text data, that confirms the client sent non-literal text where a formatted date/time value or NULL was required.

   Evidence to keep:

   Save the redacted input literal, target data type, and the row returned by pg_input_error_info.

   Redact personal birthdates or customer identifiers from logs before sharing, and never replace unparseable dates with arbitrary fallback dates.

   Evidence: PostgreSQL error and notice fields · PostgreSQL 18 date/time types · PostgreSQL 18 system information functions

3. 3

   Verify time zone names and column datetime precision in the catalog

   Tools required

   Who:

   Your developer or DBA checking timezone tokens and target column metadata.

   How:

   Query information_schema.columns for data_type, datetime_precision, and interval_type on the target column, and check any timezone token against pg_timezone_names or pg_timezone_abbrevs.

   Result that matters:

   information_schema.columns confirms whether the target is date, time, timestamp without time zone, timestamp with time zone, or interval, and the catalog views confirm whether the timezone token is recognized.

   If a timezone name or abbreviation in the input string is absent from pg_timezone_names and pg_timezone_abbrevs, that confirms an unrecognized timezone specification caused 22007.

   If the target column is interval and the input mixes units or omits the leading P in ISO 8601 format, that points to an interval syntax mismatch under Section 8.5.4.

   Evidence to keep:

   Keep the information_schema.columns row and the lookup result from pg_timezone_names or pg_timezone_abbrevs.

   Perform only SELECT lookups on system views; never alter column types to unvalidated text just to accept broken date strings.

   Evidence: PostgreSQL 18 date/time types · PostgreSQL 18 information_schema columns view

A code is meaningful only inside the right product and version context.

In Appendix A Table A.1, the PostgreSQL server maps 22007 to invalid_datetime_format in Class 22 Data Exception. Section 8.5.1 explains that while PostgreSQL accepts ISO 8601, SQL-compatible, and traditional date/time input, the ordering of day, month, and year in some formats is ambiguous and depends on the session DateStyle parameter (MDY, DMY, or YMD). For example, Table 8.10 shows that 1/18/1999 is interpreted as January 18 in MDY mode but rejected in other modes, whereas ISO 8601 (1999-01-08) is unambiguous in any mode. Code 22007 alone does not prove a server or table defect; it indicates the input string did not match the target date/time parser rules.

• Mediumsource verified

  Never store dates as unconstrained text columns or substitute a dummy date like 1970-01-01 to bypass SQLSTATE 22007, and avoid baking literal strings 'now', 'today', 'tomorrow', or 'yesterday' into prepared statements, views, or function definitions where Section 8.5.1.4 warns they become stale.

  Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL 18 date/time types

• Lowsource verified

  Do not attempt further writes in the same transaction after 22007 without issuing ROLLBACK first, and always redact personal dates and connection strings before sharing query logs.

  Evidence: PostgreSQL error and notice fields · PostgreSQL ROLLBACK

1. 1

   PostgreSQL 18 Appendix A: Error Codes

   PostgreSQL Global Development Group · 2026 · official_doc

2. 2

   PostgreSQL error and notice fields

   PostgreSQL Global Development Group · 2026 · official_doc