Conversion rules

GoogleSQL for BigQuery supports conversion. Conversion includes, but is not limited to, casting, coercion, and supertyping.

  • Casting is explicit conversion and uses the CAST() function.
  • Coercion is implicit conversion, which GoogleSQL performs automatically under the conditions described below.
  • A supertype is a common type to which two or more expressions can be coerced.

There are also conversions that have their own function names, such as PARSE_DATE(). To learn more about these functions, see Conversion functions

Comparison of casting and coercion

The following table summarizes all possible cast and coercion possibilities for GoogleSQL data types. The Coerce to column 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 Coerce 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 GoogleSQL 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

GoogleSQL 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

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

Parameter coercion

GoogleSQL supports the following parameter coercions:

Input data type Result data type
STRING parameter DATE
DATETIME
TIME