"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.