Conversion functions in Standard SQL

BigQuery supports the following conversion functions. These data type conversions are explicit, but some conversions can happen implicitly. You can learn more about implicit and explicit conversion here.

CAST overview

CAST(expression AS typename)

Description

Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type.

When using CAST, a query can fail if BigQuery is unable to perform the cast. If you want to protect your queries from these types of errors, you can use SAFE_CAST.

Casts between supported types that do not successfully map from the original value to the target domain produce runtime errors. For example, casting BYTES to STRING where the byte sequence is not valid UTF-8 results in a runtime error.

Examples

The following query results in "true" if x is 1, "false" for any other non-NULL value, and NULL if x is NULL.

CAST(x=1 AS STRING)

CAST AS ARRAY

CAST(expression AS ARRAY<element_type>)

Description

BigQuery supports casting to ARRAY. The expression parameter can represent an expression for these data types:

  • ARRAY

Conversion rules

From To Rule(s) when casting x
ARRAY ARRAY Must be the exact same ARRAY type.

CAST AS BIGNUMERIC

CAST(expression AS BIGNUMERIC)

Description

BigQuery supports casting to BIGNUMERIC. The expression parameter can represent an expression for these data types:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

Conversion rules

From To Rule(s) when casting x
FLOAT64 BIGNUMERIC The floating point number will round half away from zero. Casting a NaN, +inf or -inf will return an error. Casting a value outside the range of BIGNUMERIC will return an overflow error.
STRING BIGNUMERIC The numeric literal contained in the STRING must not exceed the maximum precision or range of the BIGNUMERIC type, or an error will occur. If the number of digits after the decimal point exceeds 38, then the resulting BIGNUMERIC value will round half away from zero to have 38 digits after the decimal point.

CAST AS BOOL

CAST(expression AS BOOL)

Description

BigQuery supports casting to BOOL. The expression parameter can represent an expression for these data types:

  • INT64
  • BOOL
  • STRING

Conversion rules

From To Rule(s) when casting x
INT64 BOOL Returns FALSE if x is 0, TRUE otherwise.
STRING BOOL Returns TRUE if x is "true" and FALSE if x is "false"
All other values of x are invalid and throw an error instead of casting to BOOL.
STRINGs are case-insensitive when converting to BOOL.

CAST AS BYTES

CAST(expression AS BYTES)

Description

BigQuery supports casting to BYTES. The expression parameter can represent an expression for these data types:

  • BYTES
  • STRING

Conversion rules

From To Rule(s) when casting x
STRING BYTES STRINGs are cast to BYTES using UTF-8 encoding. For example, the STRING "©", when cast to BYTES, would become a 2-byte sequence with the hex values C2 and A9.

CAST AS DATE

CAST(expression AS DATE)

Description

BigQuery supports casting to DATE. The expression parameter can represent an expression for these data types:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Conversion rules

From To Rule(s) when casting x
STRING DATE When casting from string to date, the string must conform to the supported date literal format, and is independent of time zone. If the string expression is invalid or represents a date that is outside of the supported min/max range, then an error is produced.
TIMESTAMP DATE Casting from a timestamp to date effectively truncates the timestamp as of the default time zone.

CAST AS DATETIME

CAST(expression AS DATETIME)

Description

BigQuery supports casting to DATETIME. The expression parameter can represent an expression for these data types:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Conversion rules

From To Rule(s) when casting x
STRING DATETIME When casting from string to datetime, the string must conform to the supported datetime literal format, and is independent of time zone. If the string expression is invalid or represents a datetime that is outside of the supported min/max range, then an error is produced.
TIMESTAMP DATETIME Casting from a timestamp to datetime effectively truncates the timestamp as of the default time zone.

CAST AS FLOAT64

CAST(expression AS FLOAT64)

Description

BigQuery supports casting to floating point types. The expression parameter can represent an expression for these data types:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

Conversion rules

From To Rule(s) when casting x
INT64 FLOAT64 Returns a close but potentially not exact floating point value.
NUMERIC FLOAT64 NUMERIC will convert to the closest floating point number with a possible loss of precision.
BIGNUMERIC FLOAT64 BIGNUMERIC will convert to the closest floating point number with a possible loss of precision.
STRING FLOAT64 Returns x as a floating point value, interpreting it as having the same form as a valid floating point literal. Also supports casts from "[+,-]inf" to [,-]Infinity, "[+,-]infinity" to [,-]Infinity, and "[+,-]nan" to NaN. Conversions are case-insensitive.

CAST AS INT64

CAST(expression AS INT64)

Description

BigQuery supports casting to integer types. The expression parameter can represent an expression for these data types:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BOOL
  • STRING

Conversion rules

From To Rule(s) when casting x
FLOAT64 INT64 Returns the closest integer value.
Halfway cases such as 1.5 or -0.5 round away from zero.
BOOL INT64 Returns 1 if x is TRUE, 0 otherwise.
STRING INT64 A hex string can be cast to an integer. For example, 0x123 to 291 or -0x123 to -291.

Examples

If you are working with hex strings (0x123), you can cast those strings as integers:

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123    | -291       |
+-----------+------------+

CAST AS NUMERIC

CAST(expression AS NUMERIC)

Description

BigQuery supports casting to NUMERIC. The expression parameter can represent an expression for these data types:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

Conversion rules

From To Rule(s) when casting x
FLOAT64 NUMERIC The floating point number will round half away from zero. Casting a NaN, +inf or -inf will return an error. Casting a value outside the range of NUMERIC will return an overflow error.
STRING NUMERIC The numeric literal contained in the STRING must not exceed the maximum precision or range of the NUMERIC type, or an error will occur. If the number of digits after the decimal point exceeds nine, then the resulting NUMERIC value will round half away from zero to have nine digits after the decimal point.

CAST AS STRING

CAST(expression AS STRING)

Description

BigQuery supports casting to STRING. The expression parameter can represent an expression for these data types:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BOOL
  • BYTES
  • TIME
  • DATE
  • DATETIME
  • TIMESTAMP
  • STRING

Conversion rules

From To Rule(s) when casting x
FLOAT64 STRING Returns an approximate string representation.
BOOL STRING Returns "true" if x is TRUE, "false" otherwise.
BYTES STRING Returns x interpreted as a UTF-8 STRING.
For example, the BYTES literal b'\xc2\xa9', when cast to STRING, is interpreted as UTF-8 and becomes the unicode character "©".
An error occurs if x is not valid UTF-8.
TIME STRING Casting from a time type to a string is independent of time zone and is of the form HH:MM:SS.
DATE STRING Casting from a date type to a string is independent of time zone and is of the form YYYY-MM-DD.
DATETIME STRING Casting from a datetime type to a string is independent of time zone and is of the form YYYY-MM-DD HH:MM:SS.
TIMESTAMP STRING When casting from timestamp types to string, the timestamp is interpreted using the default time zone, UTC. The number of subsecond digits produced depends on the number of trailing zeroes in the subsecond part: the CAST function will truncate zero, three, or six digits.

Examples

SELECT CAST(CURRENT_DATE() AS STRING) AS current_date

+---------------+
| current_date  |
+---------------+
| 2021-03-09    |
+---------------+

CAST AS STRUCT

CAST(expression AS STRUCT)

Description

BigQuery supports casting to STRUCT. The expression parameter can represent an expression for these data types:

  • STRUCT

Conversion rules

From To Rule(s) when casting x
STRUCT STRUCT Allowed if the following conditions are met:
  1. The two STRUCTs have the same number of fields.
  2. The original STRUCT field types can be explicitly cast to the corresponding target STRUCT field types (as defined by field order, not field name).

CAST AS TIME

CAST(expression AS TIME)

Description

BigQuery supports casting to TIME. The expression parameter can represent an expression for these data types:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Conversion rules

From To Rule(s) when casting x
STRING TIME When casting from string to time, the string must conform to the supported time literal format, and is independent of time zone. If the string expression is invalid or represents a time that is outside of the supported min/max range, then an error is produced.

CAST AS TIMESTAMP

CAST(expression AS TIMESTAMP)

Description

BigQuery supports casting to TIMESTAMP. The expression parameter can represent an expression for these data types:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

Conversion rules

From To Rule(s) when casting x
STRING TIMESTAMP When casting from string to a timestamp, string_expression must conform to the supported timestamp literal formats, or else a runtime error occurs. The string_expression may itself contain a time zone.
If there is a time zone in the string_expression, that time zone is used for conversion, otherwise the default time zone, UTC, is used. If the string has fewer than six digits, then it is implicitly widened.
An error is produced if the string_expression is invalid, has more than six subsecond digits (i.e. precision greater than microseconds), or represents a time outside of the supported timestamp range.
DATE TIMESTAMP Casting from a date to a timestamp interprets date_expression as of midnight (start of the day) in the default time zone, UTC.
DATETIME TIMESTAMP Casting from a datetime to a timestamp interprets datetime_expression as of midnight (start of the day) in the default time zone, UTC.

Examples

The following example casts a string-formatted timestamp as a timestamp:

SELECT CAST("2020-06-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp

-- Results depend upon where this query was executed.
+-----------------------------+
| as_timestamp                |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+

SAFE_CAST

SAFE_CAST(expression AS typename)

Description

When using CAST, a query can fail if BigQuery is unable to perform the cast. For example, the following query generates an error:

SELECT CAST("apple" AS INT64) AS not_a_number;

If you want to protect your queries from these types of errors, you can use SAFE_CAST. SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

If you are casting from bytes to strings, you can also use the function, SAFE_CONVERT_BYTES_TO_STRING. Any invalid UTF-8 characters are replaced with the unicode replacement character, U+FFFD. See SAFE_CONVERT_BYTES_TO_STRING for more information.

Other conversion functions

You can learn more about these conversion functions elsewhere in the documentation:

Conversion function From To
ARRAY_TO_STRING ARRAY STRING
DATE Various data types DATE
DATETIME Various data types DATETIME
FROM_BASE32 STRING BYTEs
FROM_BASE64 STRING BYTES
FROM_HEX STRING BYTES
PARSE_DATE STRING DATE
PARSE_DATETIME STRING DATETIME
PARSE_TIME STRING TIME
PARSE_TIMESTAMP STRING TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING BYTES STRING
STRING TIMESTAMP STRING
TIME Various data types TIME
TIMESTAMP Various data types TIMESTAMP
TO_BASE32 BYTES STRING
TO_BASE64 BYTES STRING
TO_HEX BYTES STRING