BigQuery conversion functions: every type-to-type conversion

CAST and SAFE_CAST

CAST(x AS type) handles most conversions and stops the query on a value it cannot convert. SAFE_CAST returns NULL for that value instead.

SELECT
  CAST('42' AS INT64),            -- 42
  CAST(42 AS STRING),             -- '42'
  SAFE_CAST('1,200' AS INT64),    -- NULL
  CAST(TRUE AS INT64);            -- 1

The details, including how CAST rounds floats and why '1,200' fails, are in CAST and SAFE_CAST in BigQuery.

Strings to numbers: PARSE_NUMERIC

CAST rejects a number written with thousands separators. PARSE_NUMERIC accepts it:

SELECT
  PARSE_NUMERIC('1,234.56'),       -- 1234.56
  PARSE_NUMERIC(' -12.5 '),        -- -12.5
  PARSE_BIGNUMERIC('1e3'),         -- 1000
  SAFE.PARSE_NUMERIC('12abc');     -- NULL

It ignores commas and surrounding spaces and returns an exact NUMERIC, which is the right type for money. For values that may be dirty, add the SAFE. prefix.

One caution: PARSE_NUMERIC treats the comma as a thousands separator. A European-style 1.234,56 needs its separators swapped with REPLACE first.

Strings to dates and timestamps

A string in YYYY-MM-DD form casts directly. Anything else needs a format string.

SELECT
  PARSE_DATE('%d/%m/%Y', '06/10/2026'),
  -- 2026-10-06
  PARSE_DATETIME('%Y-%m-%d %H:%M:%S',
                 '2026-10-06 09:00:00'),
  -- 2026-10-06T09:00:00
  PARSE_TIMESTAMP('%d/%m/%Y %H:%M',
                  '06/10/2026 23:30',
                  'Asia/Tokyo');
  -- 2026-10-06 14:30:00 UTC

The third argument of PARSE_TIMESTAMP is the time zone the text was written in. Leave it out and BigQuery assumes UTC, which shifts every value by the offset. That is the difference between the types in one line; the full explanation is in DATE vs DATETIME vs TIMESTAMP.

All three accept the SAFE. prefix, and on imported data they should have it. See date functions for the format elements.

Dates, timestamps and numbers to strings

SELECT
  FORMAT_DATE('%Y-%m', DATE '2026-10-06'),
  -- 2026-10
  FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ',
    TIMESTAMP '2026-10-06 23:30:00+00'),
  -- 2026-10-06T23:30:00Z
  STRING(TIMESTAMP '2026-10-06 23:30:00+00',
         'Asia/Tokyo');
  -- 2026-10-07 08:30:00+09

For numbers, CAST gives the plain value and FORMAT gives control over the layout:

SELECT
  CAST(1234567.891 AS STRING),  -- 1234567.891
  FORMAT('%.2f', 3.14159),      -- 3.14
  FORMAT("%'d", 1234567);       -- 1,234,567

Format at the last step. A formatted number is a string: it sorts alphabetically and cannot be summed. Keep the numeric column for calculations and format only what is displayed.

Unix time

SELECT
  UNIX_SECONDS(TIMESTAMP '2026-10-06 00:00:00+00'),
  -- 1791244800
  TIMESTAMP_SECONDS(1791244800),
  -- 2026-10-06 00:00:00 UTC
  TIMESTAMP_MILLIS(1791244800000);
  -- 2026-10-06 00:00:00 UTC

Check the unit before converting. Seconds, milliseconds and microseconds each have their own function, and using the wrong one gives a date in 1970 or an out-of-range error. More in timestamp functions.

Bytes and strings

SELECT
  CAST(b'hello' AS STRING),                 -- hello
  CAST('hello' AS BYTES),                   -- b'hello'
  SAFE_CONVERT_BYTES_TO_STRING(b'\xff'),    -- �
  TO_BASE64(b'hello'),                      -- aGVsbG8=
  FROM_BASE64('aGVsbG8='),                  -- b'hello'
  TO_HEX(b'hello'),                         -- 68656c6c6f
  FROM_HEX('68656c6c6f');                   -- b'hello'

CAST(bytes AS STRING) requires valid UTF-8 and fails with “Invalid cast of bytes to UTF8 string” otherwise. SAFE_CONVERT_BYTES_TO_STRING never fails: it replaces each invalid byte with the Unicode replacement character.

To move between characters and their code points:

SELECT
  TO_CODE_POINTS('ab'),             -- [97, 98]
  CODE_POINTS_TO_STRING([97, 98]);  -- ab

JSON

To turn a row or an array into JSON text:

SELECT
  TO_JSON_STRING(
    STRUCT('ana' AS customer, 120 AS amount)),
  -- {"customer":"ana","amount":120}
  TO_JSON_STRING([1, 2, 3]);
  -- [1,2,3]

To get a typed value out of JSON text, extract it as a string and cast it:

SELECT SAFE_CAST(
  JSON_VALUE('{"a":"1"}', '$.a') AS INT64);
-- 1

JSON_VALUE always returns a STRING, so a number inside JSON is still text until you cast it. This is covered in depth in JSON parsing and conversion.

Converting every element of an array

There is no single function for this. Use a subquery over UNNEST:

SELECT ARRAY(
  SELECT CAST(x AS INT64)
  FROM UNNEST(['1', '2', '3']) AS x
);
-- [1, 2, 3]

To go from an array to one string, ARRAY_TO_STRING(arr, ','); from a string to an array, SPLIT(s, ','). Both are in string functions.

When BigQuery converts for you

BigQuery performs some conversions without being asked. The one you meet most often is a string literal compared to a date:

SELECT DATE '2026-10-06' = '2026-10-06';
-- true

The literal is converted to a DATE, so WHERE order_date = '2026-10-06' works. It works only for a literal. A STRING column compared to a DATE column is an error, and you must cast one side yourself. The rules are in Google’s conversion rules.

Integers also convert to FLOAT64 or NUMERIC automatically when an expression mixes them. Nothing converts automatically from a string column to a number.

Three checks before trusting a conversion

1. Count what SAFE dropped. COUNTIF(raw IS NOT NULL AND SAFE_CAST(raw AS INT64) IS NULL) is the number of values that silently became NULL.
2. Name the time zone in every conversion to or from TIMESTAMP.
3. Use NUMERIC for money. FLOAT64 is approximate.

Where the conversion should live

A conversion decides what a column means. Whether a revenue string with a comma became 1234.56 or NULL, and which time zone turned a timestamp into a day, is settled by one function call in one query. When that call is repeated in ten reports, it is eventually written two different ways.

So do it once, in the query that prepares the data, and let reports read typed columns from there. In OWOX Data Marts that query is a Data Mart: your SQL, with its description and owner, run in your warehouse. OWOX does not convert or clean the data. It keeps the conversion you wrote as the single one in use.

Google BigQueryBigQuery array functions: syntax and tested examplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: convert data types without errorsOctober 6, 2026→Google BigQueryFull Guide to Using Wildcard Tables in BigQuery for 2025August 8, 2025→

See all articles →