"Conversion" includes, but is not limited to, casting and coercion.
- Casting is explicit conversion and uses the
- 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
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|
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.
CAST(x=1 AS STRING)
This results in
"false" for any other non-
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
|INT64||FLOAT64||Returns a close but potentially not exact FLOAT64 value.|
|NUMERIC||Floating Point||NUMERIC 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
Also supports casts from
Conversions are case-insensitive.
|STRING||NUMERIC||The numeric literal contained in the
All other values of
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.|
For example, the BYTES literal
An error occurs if
|ARRAY||ARRAY||Must be the exact same ARRAY type.|
|STRUCT||STRUCT||Allowed if the following conditions are met:
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 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
SAFE_CONVERT_BYTES_TO_STRING. Any invalid UTF-8 characters are
replaced with the unicode replacement character,
SAFE_CONVERT_BYTES_TO_STRING for more
Casting hex strings to integers
If you are working with hex strings (
0x123), you can cast those strings as
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
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
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
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
string_expression may itself contain a
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
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.
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.
BigQuery supports the following literal coercions:
|Input Data Type||Result Data Type||Notes|
|STRING literal|| DATE
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
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.
BigQuery supports the following parameter coercions:
|Input Data Type||Result Data Type|
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: