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

Source: https://docs.querylab.io/bigquery/casting-and-conversion/

Updated: 2026-10-06

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

**strings, numbers and dates**

```sql
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
```

Result in BigQuery (1 row):

| str_to_int | half_up | negative_half | int_to_str | str_to_date | float_to_numeric |
| --- | --- | --- | --- | --- | --- |
| 42 | 3 | -3 | 42 | 2026-03-01 | 1.1 |

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:

**what becomes TRUE and FALSE**

```sql
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
```

Result in BigQuery (1 row):

| from_text | zero | seven | true_as_int |
| --- | --- | --- | --- |
| true | false | true | 1 |

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

**CAST('yes' AS BOOL)**

```sql
SELECT CAST('yes' AS BOOL) AS answer
```

BigQuery rejects this query:

```text
Bad bool value: yes
```

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:

**0.1 + 0.2**

```sql
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
```

Result in BigQuery (1 row):

| float_sum | float_equal | numeric_sum | numeric_equal |
| --- | --- | --- | --- |
| 0.30000000000000004 | false | 0.3 | true |

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:

**1e40 as NUMERIC**

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

BigQuery rejects this query:

```text
Invalid NUMERIC value: 1e40
```

See [Data types](https://docs.querylab.io/bigquery/data-types/).

## Implicit coercion

These conversions happen without a cast:

**a DATE vs a literal, INT64 + FLOAT64, INT64 / INT64**

```sql
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
```

Result in BigQuery (1 row):

| date_vs_literal | int_plus_float | int_division |
| --- | --- | --- |
| true | 10.5 | 2.5 |

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

In Querylab.io, hovering a column shows its type and nullability, which is the quickest way to tell whether a comparison needs a `CAST` ([code navigation](https://docs.querylab.io/writing-sql/code-navigation/#hover)).

## Formatting and parsing with FORMAT

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

**dates and numbers with a template**

```sql
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
```

Result in BigQuery (1 row):

| parsed_date | formatted_date | formatted_number |
| --- | --- | --- |
| 2026-03-01 | 01 MAR 2026 |  1,234.50 |

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`:

**text to TIMESTAMP and back**

```sql
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
```

Result in BigQuery (1 row):

| no_zone_is_utc | with_offset | timestamp_as_text |
| --- | --- | --- |
| 2026-03-01 10:00:00 UTC | 2026-03-01 08:00:00 UTC | 2026-03-01 10:00:00+00 |

In a script or session with `SET @@time_zone`, both directions use that zone instead. See [Dates and times](https://docs.querylab.io/bigquery/dates-and-times/#daily-reports-and-the-utc-date).

## 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:

**UTF-8 bytes of 'héllo'**

```sql
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
```

Result in BigQuery (1 row):

| utf8_bytes | byte_length | char_length | hex |
| --- | --- | --- | --- |
| aMOpbGxv | 6 | 5 | 6869 |

`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:

**ARRAY<STRING> to ARRAY<INT64>**

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

BigQuery rejects this query:

```text
Casting between arrays with incompatible element types is not supported: Invalid cast from ARRAY<STRING> to ARRAY<INT64> at [2:8]
```

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](https://docs.querylab.io/bigquery/safe-functions/).

See what CAST does to a string, a float and a date. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Conversion functions](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)