CAST and type conversion in BigQuery

How CAST, SAFE_CAST and implicit coercion work in BigQuery. CAST to INT64 rounds half away from zero, only 'true' and 'false' become BOOL, FLOAT64 sums aren't exact, CAST ... FORMAT parses and formats dates and numbers, and arrays can't be cast to another element type.

BigQuery converts between types in two ways: explicitly with CAST (or SAFE_CAST, which returns NULL instead of failing), and implicitly, when it coerces a value to the type an operator or function needs. Implicit coercion is narrow, much narrower than in MySQL or Postgres: numbers convert to wider numbers, and string literals convert to dates and timestamps, but a string column never turns into a number by itself. So most conversions are a CAST you write.

The basic casts

SELECT
  CAST('42' AS INT64) AS str_to_int,
  CAST(2.5 AS INT64) AS half_up,
  CAST(-2.5 AS INT64) AS negative_half,
  CAST(42 AS STRING) AS int_to_str,
  CAST('2026-03-01' AS DATE) AS str_to_date,
  CAST(1.1 AS NUMERIC) AS float_to_numeric

Note the rounding: CAST(2.5 AS INT64) is 3 and CAST(-2.5 AS INT64) is -3. Casting to an integer rounds half away from zero rather than cutting off the fraction. Use TRUNC(x), FLOOR(x) or CEIL(x) first when you want those.

Booleans

Only the strings 'true' and 'false' (in any case) cast to BOOL. Numbers cast as 0 = false, anything else = true:

SELECT
  CAST('true' AS BOOL) AS from_text,
  CAST(0 AS BOOL) AS zero,
  CAST(7 AS BOOL) AS seven,
  CAST(TRUE AS INT64) AS true_as_int

'yes', 'Y' and '1' are errors, not true:

SELECT CAST('yes' AS BOOL) AS answer

Map such values yourself: LOWER(x) IN ('yes', 'y', '1', 'true').

FLOAT64 vs NUMERIC

FLOAT64 is binary floating point, so decimal fractions aren’t exact and equality comparisons fail in ways that look wrong. NUMERIC is an exact decimal with 38 digits of precision and 9 after the point:

SELECT
  0.1 + 0.2 AS float_sum,
  0.1 + 0.2 = 0.3 AS float_equal,
  NUMERIC '0.1' + NUMERIC '0.2' AS numeric_sum,
  NUMERIC '0.1' + NUMERIC '0.2' = NUMERIC '0.3' AS numeric_equal

Use NUMERIC (or BIGNUMERIC for more digits) for money and anything that has to add up exactly. A value too large for NUMERIC is an error:

SELECT CAST('1e40' AS NUMERIC) AS too_big

See Data types.

Implicit coercion

These conversions happen without a cast:

WITH t AS (SELECT DATE '2026-03-01' AS d, 10 AS n)
SELECT
  d = '2026-03-01' AS date_vs_literal,
  n + 0.5 AS int_plus_float,
  n / 4 AS int_division
FROM t

• An INT64 combined with a FLOAT64 or NUMERIC is widened to that type.
• / on two integers returns FLOAT64. Use DIV(a, b) for integer division.
• A string literal compared with a DATE, DATETIME, TIME or TIMESTAMP is converted to that type. A string literal compared with a number is not: amount = '30' is an error.
• A STRING column is never converted implicitly.

Formatting and parsing with FORMAT

CAST ... FORMAT converts using a format template, both from text and to text:

SELECT
  CAST('03/01/2026' AS DATE FORMAT 'MM/DD/YYYY') AS parsed_date,
  CAST(DATE '2026-03-01' AS STRING FORMAT 'DD MON YYYY') AS formatted_date,
  CAST(1234.5 AS STRING FORMAT '9,999.99') AS formatted_number

The number template 9,999.99 reserves a position for the sign, which is why the result starts with a space. Use TRIM around it to drop the padding. The FM prefix ('FM9,999.99') drops it too, but also trailing zeros: the result is 1,234.5. The older functions PARSE_DATE('%m/%d/%Y', s), FORMAT_DATE('%d %b %Y', d) and FORMAT('%.2f', x) do the same with strftime-style patterns; both styles are fine.

Timestamps and text

A string without a time zone casts to a TIMESTAMP in UTC; with an offset, the offset is applied. A TIMESTAMP cast to STRING comes out with +00:

SELECT
  CAST('2026-03-01 10:00:00' AS TIMESTAMP) AS no_zone_is_utc,
  CAST('2026-03-01 10:00:00+02' AS TIMESTAMP) AS with_offset,
  CAST(TIMESTAMP '2026-03-01 10:00:00 UTC' AS STRING) AS timestamp_as_text

In a script or session with SET @@time_zone, both directions use that zone instead. See Dates and times.

STRING and BYTES

STRING is UTF-8 text and BYTES raw bytes. Casting between them keeps the bytes. Lengths differ for non-ASCII text: é is one character but two bytes:

SELECT
  CAST('héllo' AS BYTES) AS utf8_bytes,
  BYTE_LENGTH('héllo') AS byte_length,
  CHAR_LENGTH('héllo') AS char_length,
  TO_HEX(CAST('hi' AS BYTES)) AS hex

BYTES show up in results base64-encoded. TO_HEX and FROM_HEX, TO_BASE64 and FROM_BASE64 convert explicitly.

Arrays and structs

You can’t cast an array to an array of another element type:

SELECT
  CAST(['1', '2'] AS ARRAY<INT64>) AS int_array,
  CAST(STRUCT('1' AS a) AS STRUCT<a INT64>) AS int_struct

Convert the elements one by one instead: ARRAY(SELECT CAST(x AS INT64) FROM UNNEST(arr) AS x). Structs cast field by field when the field types are castable.

SAFE_CAST

Every CAST that can fail has a SAFE_CAST that returns NULL instead. Use it on data you don’t control, and check how many values turned into NULL. See SAFE functions.