BigQuery CAST and SAFE_CAST: convert data types without errors

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.

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.

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.

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

For the rest of the conversion functions, such as PARSE_NUMERIC and TO_JSON_STRING, see conversion functions in BigQuery.

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

Google BigQueryDATE vs DATETIME vs TIMESTAMP in SQL: Which Type to ChooseOctober 6, 2026→Google BigQueryBigQuery Pricing: What Queries and Storage Cost, with ExamplesOctober 6, 2026→Google BigQueryBigQuery ALTER TABLE: Add, Rename, Drop Columns, Change TypesOctober 6, 2026→

See all articles →