---
title: "BigQuery CAST and SAFE_CAST: Syntax, Examples, Errors"
canonical: "https://www.owox.com/blog/articles/bigquery-cast-and-safe-cast"
updated: "2026-10-06"
---

# BigQuery CAST and SAFE\_CAST: convert data types without errors

CAST converts a value to another data type and stops the query when a value cannot be converted. SAFE\_CAST does the same and returns NULL instead. This guide shows both on strings, numbers and dates with results from real runs, and explains the SAFE. prefix and where it does not work.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Alyona Samovar](/team/alyona-samovar) · 6 min read

![BigQuery CAST and SAFE\_CAST: convert data types without errors](https://cdn.owox.com/www/content-marketing-article/bigquery-cast-and-safe-cast/thumbnail/1.png/public)

`CAST(expression AS type)` converts a value to another data type, and **stops the whole query** if a single value cannot be converted. `SAFE_CAST(expression AS type)` does the same conversion and returns `NULL` for the values that fail.

```
SELECT
  CAST('42' AS INT64),       -- 42
  SAFE_CAST('abc' AS INT64); -- NULL
```

`CAST('abc' AS INT64)` would end the query with “Bad int64 value: abc”. Every result on this page comes from a run in BigQuery on 6 October 2026.

**_Note:_** _Originally published in April 2024. Fully updated in October 2026 with re-run examples and a section on the SAFE. prefix._

## When to use CAST and when SAFE\_CAST

*   Use **`CAST`** when a failed conversion means the data is broken and you want to know: a pipeline step, a test, a column you control.
*   Use **`SAFE_CAST`** when the column comes from outside and some values will be dirty: a spreadsheet import, a form field, an event parameter.

`SAFE_CAST` has a cost that is easy to miss. **It turns bad values into `NULL` and tells nobody.** A sum over the result is simply smaller. So when you use it, count what it dropped:

```
SELECT
  COUNT(*) AS total_rows,
  COUNTIF(raw_value IS NOT NULL
    AND SAFE_CAST(raw_value AS INT64) IS NULL)
    AS failed_to_convert
FROM UNNEST(['42', 'abc', '1,200', NULL])
  AS raw_value;
-- 4, 2
```

Two of four rows failed. That number belongs next to any report built on a `SAFE_CAST`.

## Strings to numbers

```
SELECT
  CAST('42' AS INT64),               -- 42
  CAST('3.7' AS FLOAT64),            -- 3.7
  CAST('19.99' AS NUMERIC),          -- 19.99
  SAFE_CAST(' 42 ' AS INT64),        -- 42
  SAFE_CAST('1,200' AS INT64),       -- NULL
  SAFE_CAST('3.7' AS INT64),         -- NULL
  SAFE_CAST('' AS INT64),            -- NULL
  SAFE_CAST('12abc' AS INT64),       -- NULL
  SAFE_CAST('0x1F' AS INT64);        -- 31
```

What those results say:

*   **Surrounding spaces are ignored.** `' 42 '` converts.
*   **A thousands separator is not.** `'1,200'` fails. Remove it first: `SAFE_CAST(REPLACE(x, ',', '') AS INT64)` returns 1200.
*   **A decimal string does not cast to an integer.** `'3.7'` to `INT64` is `NULL`. Cast to `FLOAT64` or `NUMERIC` first.
*   **An empty string is not zero.** It is `NULL` after `SAFE_CAST` and an error after `CAST`.
*   A hex string with the `0x` prefix does convert.

For money, cast to **`NUMERIC`**, not `FLOAT64`. Floating point cannot hold every decimal exactly, and it cannot hold every large integer either: `CAST(9007199254740993 AS FLOAT64)` returns `9007199254740992`.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Numbers to integers: CAST rounds

```
SELECT
  CAST(3.7 AS INT64),   -- 4
  CAST(2.5 AS INT64),   -- 3
  CAST(-3.5 AS INT64);  -- -4
```

**BigQuery rounds when it casts a float to an integer, and halfway cases go away from zero.** It does not truncate, which is what many other languages do. If you want the decimal part dropped, use `TRUNC(x)` before the cast. More in [mathematical functions](/blog/articles/bigquery-mathematical-functions).

## Anything to a string

```
SELECT
  CAST(42 AS STRING),                  -- '42'
  CAST(DATE '2026-10-06' AS STRING),   -- '2026-10-06'
  CAST(TRUE AS STRING),                -- 'true'
  CAST(b'hello' AS STRING);            -- 'hello'
```

Casting to `STRING` almost never fails. The exception is `BYTES` that are not valid UTF-8: `CAST(b'\xff' AS STRING)` is an error and `SAFE_CAST` of the same value is `NULL`.

To control the layout, add a `FORMAT` clause:

```
SELECT CAST(DATE '2026-10-06'
  AS STRING FORMAT 'DD/MM/YYYY');
-- '06/10/2026'
```

## Strings to dates

```
SELECT
  CAST('2026-10-06' AS DATE),          -- 2026-10-06
  SAFE_CAST('2026-02-30' AS DATE),     -- NULL
  CAST('06/10/2026'
    AS DATE FORMAT 'DD/MM/YYYY');      -- 2026-10-06
```

Without a `FORMAT` clause, a string must be in `YYYY-MM-DD` form to cast to a `DATE`. For any other layout either add `FORMAT`, or use `PARSE_DATE` with a format string. Both are covered with their week and time zone traps in [BigQuery date functions](/blog/articles/bigquery-date-functions).

A timestamp casts to a date in UTC. `CAST(TIMESTAMP '2026-10-06 23:30:00+00' AS DATE)` is `2026-10-06`, although it is already 7 October in Tokyo. When the day matters, use `DATE(ts, 'Asia/Tokyo')`, which lets you name the time zone.

## Booleans

```
SELECT
  CAST('true' AS BOOL),      -- true
  CAST(1 AS BOOL),           -- true
  SAFE_CAST('yes' AS BOOL);  -- NULL
```

Only the strings `true` and `false` convert, in any letter case. `'yes'`, `'Y'` and `'1'` do not: map them with a `CASE` expression.

## The SAFE. prefix is not SAFE\_CAST

`SAFE_CAST` is one function. **`SAFE.` is a prefix** you can put in front of many other functions to get `NULL` where they would raise an error.

```
SELECT
  SAFE.PARSE_DATE('%d/%m/%Y', '31/02/2026'),
  -- NULL
  SAFE.FROM_BASE64('not base64!'),
  -- NULL
  SAFE.SUBSTR('abc', 0, -2);
  -- NULL
```

Google documents the prefix under [function calls](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference#safe_prefix). A few functions have a dedicated safe version instead, such as `SAFE_DIVIDE(10, 0)`, which returns `NULL` where `10 / 0` is an error, and `SAFE_CONVERT_BYTES_TO_STRING`.

### “SAFE with function date is not supported”

The prefix does not work everywhere. `SAFE.DATE(string_column)` returns this error instead of a date. The fix is one of the two functions that are built for it:

```
SELECT
  SAFE_CAST(s AS DATE),
  SAFE.PARSE_DATE('%Y-%m-%d', s)
FROM UNNEST(['2026-02-30']) AS s;
-- NULL, NULL
```

`SAFE_CAST` for the `YYYY-MM-DD` form, `SAFE.PARSE_DATE` for anything else.

## What can be cast to what

The pairs people need most. The full matrix is in Google’s [conversion rules](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules).

| From | To | Works when |
| --- | --- | --- |
| `STRING` | `INT64` | whole number, optional sign, optional `0x` |
| `STRING` | `FLOAT64`, `NUMERIC` | a valid decimal number |
| `STRING` | `DATE` | `YYYY-MM-DD`, or a `FORMAT` clause |
| `STRING` | `BOOL` | `true` or `false` |
| `FLOAT64` | `INT64` | always; rounds half away from zero |
| `INT64` | `BOOL` | always; 0 is false, anything else true |
| `TIMESTAMP` | `DATE` | always; in UTC |
| `BYTES` | `STRING` | valid UTF-8 |
| anything | `STRING` | nearly always |

For the rest of the conversion functions, such as `PARSE_NUMERIC` and `TO_JSON_STRING`, see [conversion functions in BigQuery](/blog/articles/bigquery-conversion-functions).

## Errors and what they mean

**“Bad int64 value”.** A string that is not a whole number went into `CAST(... AS INT64)`. Find the offenders with `WHERE SAFE_CAST(x AS INT64) IS NULL AND x IS NOT NULL`.

**“Invalid date”.** The string is not in `YYYY-MM-DD` form, or the date does not exist, such as 30 February.

**“Invalid cast from X to Y”.** That pair is not allowed at all, for example `DATE` to `INT64`. `SAFE_CAST` does not help here: it protects against bad values, not against an impossible conversion. Use the function made for it, in this case `UNIX_DATE`.

**“SAFE with function date is not supported”.** See above.

## One cast, many reports

Type conversion is where a clean-looking number is usually decided. Whether `'1,200'` became 1200 or `NULL`, whether a price went through `FLOAT64`, whether an evening order was dated in UTC: each choice is one function call, made once, by whoever wrote the query.

Then the query gets copied, and the second copy makes the choice differently. That is why the casts belong in one place. In [OWOX Data Marts](https://www.owox.com/app-signup) the query that does the cleaning is a Data Mart: your SQL, with its description and owner, run in your warehouse, and every report reads the already-typed columns from it. OWOX does not clean or convert anything for you. It makes sure the conversion you wrote is the only one in use.

![Data Setup tab of the Unified Ad Spend Data Mart in OWOX Data Marts, defined as SQL on Google BigQuery, validated, with its scan size shown – the casts are written once, with a comment on why](https://cdn.owox.com/www/content-marketing-article/bigquery-cast-and-safe-cast/screens/1.png/public)

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Why does SAFE.DATE return an error in BigQuery?

With a string argument BigQuery answers 'SAFE with function date is not supported'. Use SAFE\_CAST(x AS DATE) for YYYY-MM-DD values or SAFE.PARSE\_DATE with a format string for any other layout.

Does CAST round or truncate when converting a float to an integer?

It rounds, and halfway cases go away from zero: CAST(3.7 AS INT64) is 4, CAST(2.5 AS INT64) is 3 and CAST(-3.5 AS INT64) is -4. To drop the decimal part, apply TRUNC first.

When should I use SAFE\_CAST instead of CAST?

When the column comes from outside and some values will be dirty: a spreadsheet import, a form field, an event parameter. Count the rows where the source is not NULL and the SAFE\_CAST result is NULL, because SAFE\_CAST drops bad values without telling anyone.

What are common errors when using CAST in BigQuery?

'Bad int64 value' for a string that is not a whole number, such as '1,200' or '3.7'. 'Invalid date' for a string that is not YYYY-MM-DD or a date that does not exist. 'Invalid cast from X to Y' for a pair of types that cannot be converted at all.

How do I convert a string to a date in BigQuery?

CAST(s AS DATE) or SAFE\_CAST(s AS DATE) when the string is in YYYY-MM-DD form. For another layout add a FORMAT clause, CAST(s AS DATE FORMAT 'DD/MM/YYYY'), or use PARSE\_DATE with a format string.

What is the difference between CAST and SAFE\_CAST in BigQuery?

Both convert a value to another data type. CAST stops the whole query with an error when one value cannot be converted. SAFE\_CAST returns NULL for that value and carries on.

## Who wrote this

![Alyona Samovar](https://cdn.owox.com/www/webflow/6799099d337aa8a997b03b56_Alyona-Samovar.png/public)

[Alyona Samovar](/team/alyona-samovar) · Senior Digital Analyst

Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making.

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · DATE vs DATETIME vs TIMESTAMP in SQL: Which Type to Choose · October 6, 2026](/blog/articles/bigquery-date-types-date-datetime-timestamp)

[Google BigQuery · BigQuery Pricing: What Queries and Storage Cost, with Examples · October 6, 2026](/blog/articles/bigquery-pricing)

[Google BigQuery · BigQuery ALTER TABLE: Add, Rename, Drop Columns, Change Types · October 6, 2026](/blog/articles/bigquery-modify-tables-data-definition-language)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Schedule a meeting

## Schedule in person meeting at Tip Top Toronto

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Alyona Samovar](https://www.owox.com/team/alyona-samovar) — /team/alyona-samovar.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [mathematical functions](https://www.owox.com/blog/articles/bigquery-mathematical-functions) — /blog/articles/bigquery-mathematical-functions.md
- [BigQuery date functions](https://www.owox.com/blog/articles/bigquery-date-functions) — /blog/articles/bigquery-date-functions.md
- [conversion functions in BigQuery](https://www.owox.com/blog/articles/bigquery-conversion-functions) — /blog/articles/bigquery-conversion-functions.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Google BigQuery · DATE vs DATETIME vs TIMESTAMP in SQL: Which Type to Choose · October 6, 2026](https://www.owox.com/blog/articles/bigquery-date-types-date-datetime-timestamp) — /blog/articles/bigquery-date-types-date-datetime-timestamp.md
- [Google BigQuery · BigQuery Pricing: What Queries and Storage Cost, with Examples · October 6, 2026](https://www.owox.com/blog/articles/bigquery-pricing) — /blog/articles/bigquery-pricing.md
- [Google BigQuery · BigQuery ALTER TABLE: Add, Rename, Drop Columns, Change Types · October 6, 2026](https://www.owox.com/blog/articles/bigquery-modify-tables-data-definition-language) — /blog/articles/bigquery-modify-tables-data-definition-language.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md