Conversion rules in Standard SQL

"Conversion" includes, but is not limited to, casting and coercion.

  • Casting is explicit conversion and uses the CAST() function.
  • Coercion is implicit conversion, which BigQuery performs automatically under the conditions described below.
  • There is a third group of conversion functions that have their own function names, such as UNIX_DATE().

The table below summarizes all possible CAST and coercion possibilities for BigQuery data types. "Coercion To" applies to all expressions of a given data type, (for example, a column), but literals and parameters can also be coerced. See Literal Coercion and Parameter Coercion for details.

From Type CAST to Coercion To
INT64 BOOL
INT64
NUMERIC
BIGNUMERIC
FLOAT64
STRING
 NUMERIC
BIGNUMERIC
FLOAT64
NUMERIC INT64
NUMERIC
BIGNUMERIC
FLOAT64
STRING
 BIGNUMERIC
FLOAT64
BIGNUMERIC INT64
NUMERIC
BIGNUMERIC
FLOAT64
STRING
 FLOAT64
FLOAT64 INT64
NUMERIC
BIGNUMERIC
FLOAT64
STRING
  
BOOL BOOL
INT64
STRING
  
STRING BOOL
INT64
NUMERIC
BIGNUMERIC
FLOAT64
STRING
BYTES
DATE
DATETIME
TIME
TIMESTAMP
  
BYTES STRING
BYTES
  
DATE STRING
DATE
DATETIME
TIMESTAMP
  
DATETIME STRING
DATE
DATETIME
TIME
TIMESTAMP
  
TIME STRING
TIME
  
TIMESTAMP STRING
DATE
DATETIME
TIME
TIMESTAMP
  
ARRAY ARRAY  
STRUCT STRUCT  

Casting

Syntax:

CAST(expr AS typename)

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

Example:

CAST(x=1 AS STRING)

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

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.

When casting an expression x of the following types, these rules apply:

From To Rule(s) when casting x
INT64 FLOAT64 Returns a close but potentially not exact FLOAT64 value.
INT64 BOOL Returns FALSE if x is 0, TRUE otherwise.
NUMERIC Floating Point NUMERIC will convert to the closest floating point number with a possible loss of precision.
BIGNUMERIC Floating Point BIGNUMERIC will convert to the closest floating point number with a possible loss of precision.
FLOAT64 INT64 Returns the closest INT64 value.
Halfway cases such as 1.5 or -0.5 round away from zero.
FLOAT64 STRING Returns an approximate string representation.
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.
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.
BOOL INT64 Returns 1 if x is TRUE, 0 otherwise.
BOOL STRING Returns "true" if x is TRUE, "false" otherwise.
STRING FLOAT64 Returns x as a FLOAT64 value, interpreting it as having the same form as a valid FLOAT64 literal.
Also supports casts from "inf", "+inf", "-inf", and "nan".
Conversions are case-insensitive.
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.
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.
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.
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.
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.
ARRAY ARRAY Must be the exact same ARRAY type.
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).

Safe casting

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.

Casting hex strings to integers

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

Casting time types

BigQuery supports casting time types to/from strings as follows:

CAST(time_expression AS STRING)
CAST(string_expression AS TIME)

Casting from a time type to a string is independent of time zone and is of the form HH:MM:SS. 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.

Casting date types

BigQuery supports casting date types to/from strings as follows:

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

Casting from a date type to a string is independent of time zone and is of the form YYYY-MM-DD. 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.

Casting datetime types

BigQuery supports casting datetime types to/from strings as follows:

CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)

Casting from a datetime type to a string is independent of time zone and is of the form YYYY-MM-DD HH:MM:SS. 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.

Casting timestamp types

BigQuery supports casting timestamp types to/from strings as follows:

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

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.

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—see time zones. 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.

Casting between date, datetime and timestamp types

BigQuery supports casting between date, datetime and timestamp types as shown in the conversion rules table.

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

Casting from a date to a timestamp interprets date_expression as of midnight (start of the day) in the default time zone, UTC. Casting from a timestamp to date effectively truncates the timestamp as of the default time zone.

CAST(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)

Casting from a datetime to a timestamp interprets datetime_expression as of midnight (start of the day) in the default time zone, UTC.

Coercion

BigQuery coerces the result type of an expression to another type if needed to match function signatures. For example, if function func() is defined to take a single argument of type INT64 and an expression is used as an argument that has a result type of FLOAT64, then the result of the expression will be coerced to INT64 type before func() is computed.

Literal coercion

BigQuery supports the following literal coercions:

Input Data Type Result Data Type Notes
STRING literal DATE
DATETIME
TIME
TIMESTAMP

Literal coercion is needed when the actual literal type is different from the type expected by the function in question. For example, if function func() takes a DATE argument, then the expression func("2014-09-27") is valid because the STRING literal "2014-09-27" is coerced to DATE.

Literal conversion is evaluated at analysis time, and gives an error if the input literal cannot be converted successfully to the target type.

Note: String literals do not coerce to numeric types.

Parameter coercion

BigQuery supports the following parameter coercions:

Input Data Type Result Data Type
STRING parameter

If the parameter value cannot be coerced successfully to the target type, an error is provided.

Additional conversion functions

BigQuery provides the following additional conversion functions: