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