22007 — invalid_datetime_format

22007 is invaliddatetimeformat. The fixed interval-formatting path reports an invalid format specification and explains that intervals are not tied to specific calendar dates.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

8.1 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 18 19 20

aliases: Unknown
class name: Data Exception
condition name: invalid_datetime_format
lang: en
sqlstate: 22007

Definition PG 18

22007

At a glance {#at-a-glance}

22007 is invalid_datetime_format. The fixed interval-formatting path reports an invalid format specification and explains that intervals are not tied to specific calendar dates.

The shared case formats interval '1 day' with the invalid ID mask, then uses DD as the repaired interval format. Send the two SELECT statements separately; the first is expected to fail before the repaired format runs. This verifies the interval-formatting path only; the runner owns session cleanup.

SELECT to_char(interval '1 day', 'ID');
SELECT to_char(interval '1 day', 'DD');

Calibration observed the interval DCH formatting path: an invalid format reported invalid format specification for an interval value with hint Intervals are not tied to specific calendar dates.; a valid DD format returned 01, and both runner autocommit sessions returned to IDLE. This run covers interval formatting only; general date parsing and DateStyle remain source/document scope.

Messages {#messages}

The INVALID_FOR_INTERVAL guard raises ERROR with primary invalid format specification for an interval value and HINT Intervals are not tied to specific calendar dates. The cited branch has no separate DETAIL. In the fixed datetime input parser, the DTERR_BAD_FORMAT/default branch maps 22007 to the common primary template invalid input syntax for type %s: "%s"; the adjacent field-overflow and month/day-overflow branches use 22008 instead, with the latter adding HINT Perhaps you need a different "DateStyle" setting. Other datetime-format parsers can use 22007 with different primary/detail/hint variants, so retain the full diagnostic.

Meaning {#meaning}

22007 means the datetime input or format specification is invalid for the selected conversion. The fixed 18.6 interval-formatting path reports invalid format specification for an interval value and explains that intervals are not tied to specific calendar dates. In this DCH path, ID is a calendar-specific day-of-week token rejected for an interval, while DD is accepted. Fixed date input parsing routes text through ParseDateTime/DecodeDateTime and DateTimeParseError: the bad-format/default branch uses invalid input syntax for type %s: "%s"; field overflow belongs to 22008, whose month/day ambiguity branch can suggest a different DateStyle. The 18.6 documentation says DateStyle selects the interpretation order for ambiguous numeric input. DateTimeParseError can fill an ErrorSaveContext instead of throwing, so soft-validation callers differ from normal cast/input calls that propagate ERROR.

Diagnosis {#diagnosis}

Record the original text, target type, DateStyle, TimeZone, format mask, and whether the operation was a cast, input function, to_date/to_timestamp, or interval formatting. Test the same text under the deployed session settings before changing the data. Distinguish an invalid token or separator from a field such as month/day that parsed but is out of range; those paths may produce 22008 instead.

Response {#response}

Make the input unambiguous—prefer an explicit format or ISO form where appropriate—and set session parsing options deliberately at the application boundary. For interval formatting, use a mask supported for intervals rather than a calendar-date mask. The frozen case used autocommit, so the failed formatting call left the session IDLE; inside an explicit transaction, roll back the transaction or roll back to a pre-existing savepoint before continuing. Reject or repair invalid text before writing; do not silently reinterpret it under a different DateStyle.

Versions {#versions}

The locked catalogue records this condition from 7.4 in the listed formal snapshots and 19beta3; fixed source coverage is PostgreSQL 18.6.

Related {#related}

22008 for datetime field/range overflow, 22009 for timezone displacement, and 22003 for numeric range failures.

Sources {#sources}

The fixed interval-formatting path is formatting.c#L555-L558. Fixed date input dispatch is in date.c#L110-L178, and the common parser error mapping is in datetime.c#L4195-L4266. PostgreSQL 18's date/time input documentation documents DateStyle and ambiguous date text. The structured evidence record pins the fixed sources, message, and hint.

Compare versions

SQLSTATE evidence and recorded runtime versions

cases · invalid_interval_format

{
  "assertions": [
    "SQLSTATE is 22007",
    "The diagnostic and HINT identify the interval format restriction",
    "The failed autocommit session remains IDLE",
    "A valid interval format returns the expected value afterward"
  ],
  "case_id": "invalid_interval_format",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [],
  "repair": "Use an interval-compatible format such as DD, or convert a value whose semantics really require a calendar date before formatting.",
  "trigger": "Format an interval with the date-only field ID, which is not defined for intervals.",
  "versions": [
    "10",
    "18"
  ]
}

claims · identity

{
  "claim_id": "identity",
  "limits": "",
  "method": "Read fixed definition.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "22007 is invalid_datetime_format in SQLSTATE Class 22."
}

claims · mechanism.interval-format

{
  "claim_id": "mechanism.interval-format",
  "limits": "The selected runtime covers interval formatting only.",
  "method": "Read the INVALID_FOR_INTERVAL macro and both message fields.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.path.interval-format"
  ],
  "statement": "The interval/time formatting guard rejects calendar-specific format tokens for interval values and supplies a fixed hint explaining the calendar boundary."
}

claims · mechanism.datetime-input

{
  "claim_id": "mechanism.datetime-input",
  "limits": "This confirms source and documentation scope only; the selected runtime covers interval formatting, not DateStyle parsing.",
  "method": "Read the fixed date input/parser and error mapping, then the same-version documentation.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.path.date-input",
    "src.path.datetime-error",
    "doc.datetime-input.18.6"
  ],
  "statement": "Fixed date input dispatches text through ParseDateTime and DecodeDateTime. DateTimeParseError maps the DTERR_BAD_FORMAT/default branch to 22007 with the common primary invalid input syntax for type template; field overflow and month/day overflow use 22008, with the latter adding a DateStyle hint. The PostgreSQL 18 documentation describes DateStyle as selecting the interpretation order for ambiguous numeric date input, and the parser can fill ErrorSaveContext instead of throwing for soft-validation callers."
}

claims · history

{
  "claim_id": "history",
  "limits": "",
  "method": "Use catalogue boundary.",
  "position": 3,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "The locked catalogue records 22007 from 7.4 without proving an exact implementation introduction date."
}

claims · runtime.observation

{
  "claim_id": "runtime.observation",
  "limits": "The observation does not generalize to DateStyle parsing or every date/time formatter.",
  "method": "Execute the shared registry and inspect selected summaries.",
  "position": 4,
  "runtime": [
    "runtime.22007-data1-latest.latest",
    "runtime.22007-data1-pg10.pg10"
  ],
  "sources": [
    "manifest.22007",
    "snippet-registry.22007"
  ],
  "statement": "The invalid_interval_format case passed on isolated PostgreSQL 18.6 and 10.21: the interval mask ID raised 22007 with the fixed hint, left the autocommit session IDLE, and DD returned 01."
}

messages · message.interval

{
  "limits": "",
  "message_id": "message.interval",
  "path": "",
  "position": 0,
  "raw": {
    "hint_template": "Intervals are not tied to specific calendar dates.",
    "id": "message.interval",
    "primary_template": "invalid format specification for an interval value",
    "severity_source": "ERROR",
    "sources": [
      "src.path.interval-format"
    ],
    "sqlstate": "22007"
  },
  "severity": "ERROR",
  "sources": [
    "src.path.interval-format"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "invalid format specification for an interval value",
      "position": 0,
      "role": "",
      "template": "invalid format specification for an interval value"
    },
    {
      "kind": "hint",
      "literal": "Intervals are not tied to specific calendar dates.",
      "position": 1,
      "role": "",
      "template": "Intervals are not tied to specific calendar dates."
    }
  ]
}

messages · message.datetime-input

{
  "limits": "",
  "message_id": "message.datetime-input",
  "path": "",
  "position": 1,
  "raw": {
    "id": "message.datetime-input",
    "primary_template": "invalid input syntax for type %s: \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.path.datetime-error"
    ],
    "sqlstate": "22007"
  },
  "severity": "ERROR",
  "sources": [
    "src.path.datetime-error"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "invalid input syntax for type : \" \"",
      "position": 0,
      "role": "",
      "template": "invalid input syntax for type %s: \"%s\""
    }
  ]
}

runtimes · runtime.22007-data1-latest.latest · 18.6 (Homebrew) · passed

{
  "cases": [
    "invalid_interval_format"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "cases": [
      "invalid_interval_format"
    ],
    "id": "runtime.22007-data1-latest.latest",
    "observed": {
      "sqlstate": "22007",
      "status_after_repair": "IDLE"
    },
    "raw": "verify/results/22007-data1-latest/latest/raw.jsonl",
    "raw_sha256": "e1ab1c24625152560f9dc7baf091dbec22324d34bba0deea9c8048565500906a",
    "run_id": "22007-data1-latest",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/22007/snippets.json",
      "sha256": "cea6f9f6a07e3371f1ebc79d2842da2c66c06750f3b39f91b7fdd57fb2b7d0a9"
    },
    "status": "passed",
    "summary": "verify/results/22007-data1-latest/latest/summary.json",
    "summary_sha256": "5429879e671be538f513654b5c0821f227aac67f67e0b1fbb2d6c7821c0af675",
    "target": "latest"
  },
  "run_id": "22007-data1-latest",
  "runtime_id": "runtime.22007-data1-latest.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}

runtimes · runtime.22007-data1-pg10.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed

{
  "cases": [
    "invalid_interval_format"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "cases": [
      "invalid_interval_format"
    ],
    "id": "runtime.22007-data1-pg10.pg10",
    "observed": {
      "sqlstate": "22007",
      "status_after_repair": "IDLE"
    },
    "raw": "verify/results/22007-data1-pg10/pg10/raw.jsonl",
    "raw_sha256": "e9f8cef16bd8674c68fb7c518fa325adbf726e8797dc8b43da20508a0ecc41dd",
    "run_id": "22007-data1-pg10",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/22007/snippets.json",
      "sha256": "cea6f9f6a07e3371f1ebc79d2842da2c66c06750f3b39f91b7fdd57fb2b7d0a9"
    },
    "status": "passed",
    "summary": "verify/results/22007-data1-pg10/pg10/summary.json",
    "summary_sha256": "dbc6026274c92764eb19acdc4668d1aa3bb2529a01a7ca3dbcb879cb46646f9c",
    "target": "pg10"
  },
  "run_id": "22007-data1-pg10",
  "runtime_id": "runtime.22007-data1-pg10.pg10",
  "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
  "status": "passed",
  "target": "pg10"
}

sources · src.errcodes.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L179"
}

sources · src.calls.REL_18_6

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 1,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6",
  "tag": "",
  "url": ""
}

sources · src.path.interval-format

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/formatting.c",
  "position": 2,
  "sha256": "a22651cc223e93c1879420004027325579fc79a3ba8684408496149164a2220c",
  "source_id": "src.path.interval-format",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/formatting.c#L548-L559"
}

sources · src.path.date-input

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/date.c",
  "position": 3,
  "sha256": "4834c868f3461822eb0de972fe35b04f9fb9ba75cf2fa9794a67259536f35773",
  "source_id": "src.path.date-input",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/date.c#L110-L178"
}

sources · src.path.datetime-error

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/datetime.c",
  "position": 4,
  "sha256": "709e05b536f611f7eeaaabb716501d59d4d1e87c53757e15240fe1fc4845459c",
  "source_id": "src.path.datetime-error",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/datetime.c#L4195-L4266"
}

sources · doc.datetime-input.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/datatype-datetime.html#DATATYPE-DATETIME-INPUT",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/datatype.sgml",
  "position": 5,
  "sha256": "86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700",
  "source_id": "doc.datetime-input.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/datatype.sgml#L1878-L1896"
}

sources · snippet-registry.22007

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "verify/cases/22007/snippets.json",
  "position": 6,
  "sha256": "cea6f9f6a07e3371f1ebc79d2842da2c66c06750f3b39f91b7fdd57fb2b7d0a9",
  "source_id": "snippet-registry.22007",
  "tag": "",
  "url": ""
}

sources · manifest.22007

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "verify/cases/22007/cases.json",
  "position": 7,
  "sha256": "bc62fd20d13379af121cdbd76080fea124206647dfcac6c27737ceabf21b2af4",
  "source_id": "manifest.22007",
  "tag": "workspace",
  "url": "verify/cases/22007/cases.json"
}

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:0174675f9bb187bc6f0337a1d9318c28119611bbc8a8064a3e157afb4a243f2b

Text language: en · english-manuals:0174675f9bb187bc6f0337a1d9318c28119611bbc8a8064a3e157afb4a243f2b