# 22007 — invalid_datetime_format

> Source-backed reference for PostgreSQL SQLSTATE 22007.
---

# 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.

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `22007` |
| Condition | `invalid_datetime_format` |
| Status | `active` |
| Known present by | `7.4` |
| Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` |
| Macros | `ERRCODE_INVALID_DATETIME_FORMAT` |
| Aliases | `—` |

<!-- source facts: data/errcodes/22007.json -->
<!-- END SQLSTATE FACTS -->

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.

<!-- BEGIN SQLSTATE SNIPPET: invalid_interval_format -->
```sql
SELECT to_char(interval '1 day', 'ID');
SELECT to_char(interval '1 day', 'DD');
```
<!-- END SQLSTATE SNIPPET -->

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`](../22008/) for datetime field/range overflow, [`22009`](../22009/) for timezone displacement, and [`22003`](../22003/) for numeric range failures.

## Sources {#sources}

The fixed interval-formatting path is [`formatting.c#L555-L558`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/formatting.c#L555-L558). Fixed date input dispatch is in [`date.c#L110-L178`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/date.c#L110-L178), and the common parser error mapping is in [`datetime.c#L4195-L4266`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/datetime.c#L4195-L4266). PostgreSQL 18's [date/time input documentation](https://www.postgresql.org/docs/18/datatype-datetime.html#DATATYPE-DATETIME-INPUT) documents `DateStyle` and ambiguous date text. The structured [evidence record](../data/evidence/22007.json) pins the fixed sources, message, and hint.