CAST

Definition

The CAST function allows you to convert between different Data Types in BigQuery.

Syntax

CAST (expr AS typename)

Where typename is any of:

You can read more about each type here.

SELECT
  str_number,
  CAST(TRIM(str_number) AS INT64) AS number
FROM
  (
    SELECT
      '     3' AS str_number
    UNION ALL
(    SELECT
      '8 ' AS str_number)
    UNION ALL
(    SELECT
      ' 23' AS str_number)
  ) AS table_3

Practical Info

Converting a STRINGs to DATEs

When converting a STRING to DATE, the STRING must be in the format: YYYY-MM-DD

SELECT
  safe_CAST(date1 AS DATE) AS date1,
  safe_CAST(date2 AS DATE) AS date2,
  FORMAT_DATE('%b %d,%Y', safe_CAST(date2 AS DATE)) AS formatted_date2
FROM
  (
    SELECT
      '2/1/20' AS date1,
      '2020-02-01' AS date2
  ) AS table_1

In the example above date1 returns a NULL value since it's not in the right format. To re-format your strings check out the  page.

Similar rules apply for converting STRINGs to DATETIME, TIMESTAMP, and TIME:

When casting STRING -> DATETIME, the string must be in the format YYYY:MM:DD HH:MM:SS

SELECT
  CAST('2020-12-25 03:22:01' AS DATETIME) AS str_to_datetime

When casting STRING -> TIMESTAMP the string must be in a format YYYY:MM:DD HH:MM:SS [timestamp] (where the timestamp defaults to UTC if not specified)

SELECT
  CAST('2020-12-25 03:22:01-5:00' AS TIMESTAMP) AS str_to_timestamp

When casting STRING->TIME the string must be in the format HH:MM:SS

select cast('15:03:11' as TIME) as str_to_time

Converting to BOOLs

When converting to BOOLs (Booleans, True/False) from other types there are a few rules to keep in mind:

INT64 -> BOOL: Returns FALSE for 0, TRUE otherwise.

SELECT
  CAST(0 AS BOOL) AS zero_to_bool,
  CAST(5 AS BOOL) AS five_to_bool

STRING -> BOOL: Returns TRUE for 'true', FALSE for 'false', NULL otherwise.

SELECT
  SAFE_CAST('true' AS BOOL) AS true_to_bool,
  SAFE_CAST('false' AS BOOL) AS false_to_bool,
  SAFE_CAST('apples' AS BOOL) AS apples_to_bool

Common Questions

How to convert a STRING to a DATE?

Let's say you have a stringifed date that's not formatted in the standard YYYY-MM-DD, how do you cast it to be a date?

There are 2 options:

Use PARSE_DATE where you can specify the exact syntax of your string:

SELECT
  PARSE_DATE('%m/%d/%Y', '1/3/2020')

Use STRING manipulation to reconstruct your string and CAST to the right format:

SELECT
  CAST(CONCAT(REGEXP_EXTRACT(str_date, r'\/([0-9]{4})'), "-", REGEXP_EXTRACT(str_date, r'([0-9]{1,2})\/'), "-", REGEXP_EXTRACT(str_date, r'\/([0-9]{1,2})\/')) AS DATE) AS date_from_str
FROM
  (
    SELECT
      '12/3/2020' AS str_date
  ) AS table_1

How to convert EPOCH to TIMESTAMP?

Epochs, or Unix time are the seconds since 1970-01-01 00:00:00 UTC. To make them usable for analysis, you'll need to convert them to a DATETIME or TIMESTAMP.

To do this, you can again make use of PARSE_DATETIME, or PARSE_TIMESTAMP if your epoch is a STRING.

SELECT parse_timestamp('%s', '1611592773')

Or if your Epoch is an INT64, use the following functions: TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS, UNIX_SECONDS, UNIX_MILLIS, UNIX_MICROS.

SELECT
  TIMESTAMP_SECONDS(1611592773) AS formatted_timestamp

How to convert scientific notation to decimal format?

CAST to NUMERIC to turn a Scientific Notation column to a decimal:

SELECT sci, cast(sci as NUMERIC) sci_numeric FROM (
   SELECT
      (15000000.0 + 17000000.01) AS sci)

Troubleshooting Common Errors

Could not cast literal to type DATE

Check the format of your string when converting to a DATE. Remember it must be in YYYY-MM-DD . But also try PARSE_DATE if you're looking for something to read a specific format.

Related Pages