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 are also conversions that have their own function names, such as PARSE_DATE(). To learn more about these functions, see Conversion functions

Comparison chart

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
DATETIME STRING
DATE
DATETIME
TIME
TIMESTAMP
 
TIME STRING
TIME
 
TIMESTAMP STRING
DATE
DATETIME
TIME
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

Casting

Most data types can be cast from one type to another with the CAST function. 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. To learn more about the rules for CAST, SAFE_CAST and other casting functions, see Conversion functions.

Coercion

BigQuery coerces the result type of an argument expression to another type if needed to match function signatures. For example, if function func() is defined to take a single argument of type FLOAT64 and an expression is used as an argument that has a result type of INT64, then the result of the expression will be coerced to FLOAT64 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.