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.

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

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:

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

Common shapes:

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:

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:

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:

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 NULLs somewhere you will notice. See SAFE functions and CAST and conversion.

The variants of the message

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

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:

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.

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