Bad int64 value

A CAST turned a string into a number, date or timestamp and one value did not fit. The message names the value and gives no row, and a dry run never catches it. The same family covers Bad double value, Invalid date, Invalid timestamp and the compile-time Could not cast literal.

Bad int64 value: n/a

What BigQuery means

A CAST (or a function that casts, such as DATE(s) or TIMESTAMP(s)) was given a string it could not read as the target type. The text after the colon is that string. There is no row number, no column name and no position in the SQL.

It is a data error. The query is valid, a dry run passes, and the query can run for weeks until one bad value shows up. Which message you get depends on the target type:

The smallest query that reproduces it

WITH t AS (SELECT 'n/a' AS s)
SELECT CAST(s AS INT64) FROM t

The fix

First find out what the bad values are. SAFE_CAST returns NULL where CAST would fail, so rows where the value exists and the result is NULL are the ones that do not convert:

WITH t AS (
  SELECT 'a' AS id, '12' AS s UNION ALL
  SELECT 'b', '1.5' UNION ALL
  SELECT 'c', '1,234' UNION ALL
  SELECT 'd', '' UNION ALL
  SELECT 'e', NULL UNION ALL
  SELECT 'f', 'n/a'
)
SELECT id, s
FROM t
WHERE s IS NOT NULL AND SAFE_CAST(s AS INT64) IS NULL

This returns b, c, d and f. Row e is left out by the IS NOT NULL, because a NULL casts to NULL without any error.

Then pick the answer that matches what the values mean.

The bad values are rubbish and NULL is fine. Use SAFE_CAST:

SELECT SAFE_CAST(s AS INT64) AS n FROM t

This hides the problem, so count the NULLs somewhere you will see them. See SAFE functions.

The values have punctuation or spaces. Clean them first. An empty string is not a number, and NULLIF turns it into NULL:

SELECT SAFE_CAST(NULLIF(TRIM(s), '') AS INT64) AS blank_to_null,
       SAFE_CAST(REPLACE(s, ',', '') AS INT64) AS thousands
FROM t

On the data above, thousands turns 1,234 into 1234. Currency signs and percent signs need REPLACE too: $12 and 12% both fail with Bad double value as they are.

The values are decimals going into INT64. CAST('1.5' AS INT64) fails, because the string must already be an integer. Cast to FLOAT64 first and round on purpose:

SELECT CAST(ROUND(SAFE_CAST(s AS FLOAT64)) AS INT64) AS n FROM t

1.5 becomes 2. Choose ROUND, FLOOR or TRUNC deliberately. A silent truncation is the next bug.

The column should have been a number. Fix the load or the schema, so the cast is not needed. A cast on a clustered column in a WHERE also stops BigQuery from pruning blocks.

A date in the wrong shape. CAST reads the ISO form, year first, with dashes. 15/01/2024 is not that and fails with Invalid date: '15/01/2024'. Use PARSE_DATE with a format that matches. See Failed to parse input string.

The variants of the message

The value is printed as it is, so what you see tells you what is wrong.

DATE(s) on a string is read through TIMESTAMP, so its failure says Invalid timestamp: 'x', not Invalid date.

The compile-time version

Could not cast literal is a different failure. It is reported before the query runs, so a dry run shows it:

Could not cast literal "abc" to type DATE at [1:69]

It appears when a string literal is compared with, or used as, a date or time value, and the literal is not one:

WITH t AS (SELECT DATE '2024-01-01' AS d)
SELECT * FROM t WHERE d = 'abc'

The target type is named in the message: DATE, TIMESTAMP, DATETIME or TIME. It appears in =, >, BETWEEN, IN and in function arguments such as DATE_ADD('abc', INTERVAL 1 DAY). The position points at the literal.

Impossible dates are caught the same way: d > '2024-13-01' and d = '2024-02-30' both fail, and so does '01/01/2024'. A literal with a time on a DATE column, d = '2024-01-01 10:00:00', fails too.

SAFE. does not help, because there is no row to be safe about. SAFE.TIMESTAMP('x') runs and gives NULL, but SAFE.DATE('x') still stops with Could not cast literal "x" to type TIMESTAMP.

Fix the literal:

SELECT * FROM t WHERE d = '2024-01-01';
SELECT * FROM t WHERE d = DATE '2024-01-01';

The first form works because BigQuery coerces a valid date string. DATE '2024-01-01' says what you mean. A bad value in a DATE '...' literal gives a different message: Invalid DATE literal at [1:8].

SELECT 1 + 'a' fails with Could not cast literal "a" to type DATE. BigQuery tried the DATE + INT64 signature of +, and the string failed to become a date. The real mistake is the type of 'a'.

Queries that look wrong but run

A literal cast. SELECT CAST('abc' AS DATE) passes a dry run and fails only when it runs, with Invalid date: 'abc'. Literals inside CAST are not checked at compile time.

Spaces around the number. CAST(' 12 ' AS INT64) gives 12.

Scientific notation to FLOAT64. CAST('1e3' AS FLOAT64) gives 1000.0.

Short months and days. CAST('2024-1-5' AS DATE) gives 5 January 2024, and d = '2024-1-1' works too.

A NULL. It casts to NULL, with no error.

Different errors that look like this one

Failed to parse input string "...". From PARSE_DATE, PARSE_TIMESTAMP and the other PARSE_ functions. See Failed to parse input string.

Invalid cast from BOOL to DATE. There is no conversion between these types at all, so the dry run reports it with a position.

int64 out of range: 1e+30 and Illegal conversion of non-finite floating point number to an integer: nan. A FLOAT64 that does not fit an INT64.

No matching signature for operator = for argument types: INT64, STRING. A number compared with a quoted literal. BigQuery will not coerce a literal to a number. See No matching signature for operator.

In Querylab.io

The editor does not flag a bad value, because the data decides. Could not cast literal is a compile error. The editor’s own checks do not mark it as you type. BigQuery’s dry run reports it when you pause.

After a failed run, Querylab.io can offer up to three fixes for Bad int64 value and Bad double value. They appear only when all of these hold:

• The query holds exactly one plain CAST that matches the message: a CAST to an integer type (INT64, INT, INTEGER, BIGINT and similar) for Bad int64 value, a CAST to FLOAT64 for Bad double value. A SAFE_CAST does not count. With two matching casts the message cannot say which failed.
• 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 turns the CAST into SAFE_CAST. When the cast 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 values with a count for each. Skip rows that fail to convert adds a WHERE that drops them. These two need more: the cast 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.

Invalid date, Invalid timestamp, the NUMERIC messages and Could not cast literal get no button.