# 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. Source: https://docs.querylab.io/bigquery-errors/bad-int64-value/ Updated: 2026-10-30 ```text 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: | Target type | Message | | ------------ | ------------------------------- | | `INT64` | `Bad int64 value: n/a` | | `FLOAT64` | `Bad double value: n/a` | | `NUMERIC` | `Invalid NUMERIC value: n/a` | | `BIGNUMERIC` | `Invalid BIGNUMERIC value: n/a` | | `BOOL` | `Bad bool value: n/a` | | `DATE` | `Invalid date: 'n/a'` | | `TIMESTAMP` | `Invalid timestamp: 'n/a'` | | `DATETIME` | `Invalid datetime string "n/a"` | | `TIME` | `Invalid time string "n/a"` | ## The smallest query that reproduces it ```sql 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: ```sql 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`: ```sql SELECT SAFE_CAST(s AS INT64) AS n FROM t ``` This hides the problem, so count the `NULL`s somewhere you will see them. See [SAFE functions](https://docs.querylab.io/bigquery/safe-functions/). **The values have punctuation or spaces.** Clean them first. An empty string is not a number, and `NULLIF` turns it into `NULL`: ```sql 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: ```sql 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](https://docs.querylab.io/bigquery-errors/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. | Message | What the value was | | --------------------------------------- | ------------------------------------------------------------ | | `Bad int64 value: 1.5` | a decimal going into `INT64` | | `Bad int64 value: 1,234` | a thousands separator | | `Bad int64 value: 1e3` | scientific notation. `FLOAT64` accepts it, `INT64` does not. | | `Bad int64 value: 9223372036854775808` | one more than the largest `INT64` | | `Bad int64 value:` | an empty string, with nothing after the colon | | `Bad double value: $12` | a currency sign | | `Invalid date: '15/01/2024'` | a date that is not `YYYY-MM-DD` | | `Invalid date: '2024-13-45'` | the right shape, a date that does not exist | | `Invalid date: '2024-01-15T10:30:00Z'` | a timestamp string cast to `DATE` | | `Invalid timestamp: '2024-01-15 10:30'` | seconds missing. `2024-01-15 10:30:00` works. | `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: ```text 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: ```sql 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: ```sql 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](https://docs.querylab.io/bigquery-errors/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](https://docs.querylab.io/bigquery-errors/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. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Conversion functions, CAST and SAFE_CAST](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions) - [Conversion rules](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules) - [Format elements](https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements) - [Data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)