# Failed to parse input string

> A PARSE_DATE, PARSE_TIMESTAMP, PARSE_DATETIME or PARSE_TIME call got a string that does not fit its format. The message names the value that failed and gives no row, and a dry run never catches it. This page lists what the format string accepts, the cases that look wrong but parse, and how to find the bad rows.

Source: https://docs.querylab.io/bigquery-errors/failed-to-parse-input-string/

Updated: 2026-10-30

```text
Failed to parse input string "15-01-2024"
```

## What BigQuery means

A `PARSE_DATE`, `PARSE_DATETIME`, `PARSE_TIME` or `PARSE_TIMESTAMP` call read a string against its format and the two did not line up. The text in quotes is the string it could not read. That is the only clue the error gives: there is no row number, no column name and no position in the SQL.

It is a data error. The query was valid, a dry run passes, and it can work for weeks until one row arrives in a different shape.

Two things decide whether a string fits:

- **Every element must match in order.** `%Y-%m-%d` wants a year, a dash, a month, a dash and a day, in that order and nothing else.
- **The whole string must be used.** Text left over at the end, such as a time of day or a time zone, makes the parse fail.

## The smallest query that reproduces it

```sql
SELECT PARSE_DATE('%Y-%m-%d', '15-01-2024')
```

The string is day-month-year. The year takes `15`, the dashes match and the month takes `01`, and then `2024` cannot be read as a day.

## The fix

**Make the format describe the data.** For the string above, name the elements in the order they appear:

```sql
SELECT PARSE_DATE('%d-%m-%Y', '15-01-2024')
```

Common shapes:

| String                            | Format                     |
| --------------------------------- | -------------------------- |
| `2024-01-15`                      | `%Y-%m-%d`                 |
| `20240115`                        | `%Y%m%d`                   |
| `15/01/2024`                      | `%d/%m/%Y`                 |
| `15.01.2024`                      | `%d.%m.%Y`                 |
| `Jan 15, 2024`                    | `%b %d, %Y`                |
| `January 15, 2024`                | `%B %d, %Y`                |
| `2024-01-15T10:30:00`             | `%Y-%m-%dT%H:%M:%S`        |
| `2024-01-15 10:30:00.123`         | `%Y-%m-%d %H:%M:%E*S`      |
| `2024-01-15T10:30:00Z`            | `%Y-%m-%dT%H:%M:%E*S%Ez`   |
| `2024-01-15T10:30:00+01:00`       | `%Y-%m-%dT%H:%M:%E*S%Ez`   |
| `Mon, 15 Jan 2024 10:30:00 +0000` | `%a, %d %b %Y %H:%M:%S %z` |

**Strings with a time on a date column.** `PARSE_DATE` does not take `%H`, and a string with a time on the end does not fit `%Y-%m-%d`. Cut the date out, or parse the full string and take its date:

```sql
SELECT DATE(PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-01-15 10:30:00'))
```

`PARSE_DATE('%Y-%m-%d', SUBSTR(s, 1, 10))` also works when the first ten characters are always the date.

**Find the bad rows.** The message does not tell you which row it was. `SAFE.` in front of the function turns the failure into `NULL`, so you can list the strings that do not parse:

```sql
WITH t AS (
  SELECT 'a' AS id, '2024-01-15' AS s UNION ALL
  SELECT 'b', '15/01/2024' UNION ALL
  SELECT 'c', 'N/A' UNION ALL
  SELECT 'd', NULL
)
SELECT id, s
FROM t
WHERE s IS NOT NULL AND SAFE.PARSE_DATE('%Y-%m-%d', s) IS NULL
```

This returns rows `b` and `c`. The `s IS NOT NULL` is needed because a `NULL` input gives a `NULL` result in both cases.

**Data in more than one format.** Try each format and keep the first that works:

```sql
SELECT COALESCE(
  SAFE.PARSE_DATE('%Y-%m-%d', s),
  SAFE.PARSE_DATE('%d/%m/%Y', s)
) AS d
FROM t
```

Empty strings and `N/A` still give `NULL`, which is usually what you want. Be careful with formats that overlap: `01/02/2024` fits both `%d/%m/%Y` and `%m/%d/%Y`, and the first one in the list wins without a warning.

**Keep the query running.** If `NULL` is acceptable, use `SAFE.PARSE_DATE` in the query itself. It hides bad data, so count the `NULL`s somewhere you will notice. See [SAFE functions](https://docs.querylab.io/bigquery/safe-functions/) and [CAST and conversion](https://docs.querylab.io/bigquery/casting-and-conversion/).

## The variants of the message

Every one of these comes from a `PARSE_` function and a string that did not fit:

| Message                                                                                                     | What happened                                                    |
| ----------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------- |
| `Failed to parse input string "15-01-2024"`                                                                 | the usual one                                                    |
| `Failed to parse input string ""`                                                                           | an empty string. Not a `NULL`: a `NULL` input returns `NULL`.    |
| `Mismatch between format character '-' and string character '/' at string index: 4 with format: '%Y-%m-%d'` | a separator in the format is not the separator in the string     |
| `Out-of-range datetime field in parsing function`                                                           | the shape fits but the date does not exist, such as `2024-02-30` |
| `Invalid format: %H is not allowed for the DATE type.`                                                      | a time element in a `PARSE_DATE` format                          |

The `Mismatch` message is the more helpful one, because it gives the position of the first bad character and the format it was reading. In the cases probed, BigQuery used it when the string broke at a literal character of the format (a dash, a slash). When the string broke on a number or a name, such as a month of `13` or `N/A` where a year should be, the message was `Failed to parse input string`.

A month of `13` gives `Failed to parse input string "2024-13-01"`, and `13/01/2024` read with `%m/%d/%Y` fails the same way. That is the usual sign that the data is day-first and the format is month-first.

## Queries that look wrong but run

**Leading and trailing spaces.** Spaces around the string are ignored:

```sql
SELECT PARSE_DATE('%Y-%m-%d', ' 2024-01-15 ')
```

**Fewer digits than the format shows.** `%m` and `%d` accept one digit, so `2024-1-5` parses as 5 January. A three-digit month does not: `2024-001-15` fails.

**Names in any case.** `january 15, 2024` parses with `%B`, and `%b` takes the short or the full name, in any case.

**Unused parts of the type.** `PARSE_TIMESTAMP('%Y-%m-%d', '2024-01-15')` works and gives midnight UTC. Anything the format leaves out starts from `1970-01-01 00:00:00`, in the time zone you pass or UTC.

**Time zone `Z`.** `%z` takes `+HHMM` or `+HH` and rejects the letter `Z`. `%Ez` accepts `Z`, `+01:00` and `+00`.

## Different errors that look like this one

**`Bad int64 value: abc`, `Invalid timestamp: '...'`, `Invalid date: ...`.** These come from `CAST`, or from a function such as `DATE(s)` or `TIMESTAMP(s)`, on a value that does not convert. See [Bad int64 value](https://docs.querylab.io/bigquery-errors/bad-int64-value/).

**`Invalid time zone: Not/AZone`.** The third argument of `PARSE_TIMESTAMP` is not a time zone name.

**`No matching signature for function PARSE_DATE`.** The arguments are wrong, and the data is fine. The format string comes first, then the value, and both are strings: `PARSE_DATE(STRING, STRING)`. See [No matching signature for operator](https://docs.querylab.io/bigquery-errors/no-matching-signature-for-operator/) for how to read the rest of that message.

## In Querylab.io

The editor does not flag these before the query runs, because whether a string fits is decided by the data.

After a failed run, Querylab.io reads the error and can offer up to three fixes. They appear only when all of these hold:

- The error is `Failed to parse input string`.
- The query holds exactly one plain `PARSE_DATE`, `PARSE_DATETIME`, `PARSE_TIME` or `PARSE_TIMESTAMP` call. With two, the message cannot say which one failed. `SAFE.PARSE_DATE` does not count.
- The query parses without errors, has no template markup, and names its tables as `dataset.table` or `project.dataset.table`.
- Every table it reads is a known base table or external table. A view, or a table whose type is not yet loaded, switches the buttons off. So does a call to a stored function.

The fixes are:

- **Bad values become NULL** adds `SAFE.` to the call. When the call sits in a `WHERE` or `ON` condition, the button reads **Skip rows that fail to convert (safe version)**, because a `NULL` there drops the row.
- **Show values that fail to convert** offers a query that lists the failing strings with a count for each. **Skip rows that fail to convert** adds a `WHERE` that drops them. These two need more: the call is in the select list of one plain `SELECT` with a `FROM`, with no `GROUP BY`, `HAVING`, `QUALIFY`, aggregate or window function. The first also needs a query without `@` parameters.

The buttons cover only `Failed to parse input string`. The `Mismatch between format character` and `Out-of-range` messages get no button.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Date functions, PARSE_DATE](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions)

- [Timestamp functions, PARSE_TIMESTAMP](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions)

- [Format elements](https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements)

- [Conversion functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions)