Conversion functions in GoogleSQL

GoogleSQL for Spanner supports conversion functions. These data typeconversions are explicit, but some conversions can happen implicitly. You canlearn more about implicit and explicit conversionhere.

Function list

NameSummary
ARRAY_TO_STRING Produces a concatenation of the elements in an array as aSTRING value.
For more information, seeArray functions.
BOOL Converts a JSON boolean to a SQLBOOL value.
For more information, seeJSON functions.
BOOL_ARRAY Converts a JSON array of booleans to a SQLARRAY<BOOL> value.
For more information, seeJSON functions.
CAST Convert the results of an expression to the given type.
CODE_POINTS_TO_BYTES Converts an array of extended ASCII code points to aBYTES value.
For more information, seeString aggregate functions.
CODE_POINTS_TO_STRING Converts an array of extended ASCII code points to aSTRING value.
For more information, seeString aggregate functions.
DATE_FROM_UNIX_DATE Interprets anINT64 expression as the number of days since 1970-01-01.
For more information, seeDate functions.
FROM_BASE32 Converts a base32-encodedSTRING value into aBYTES value.
For more information, seeString functions.
FROM_BASE64 Converts a base64-encodedSTRING value into aBYTES value.
For more information, seeString functions.
FROM_HEX Converts a hexadecimal-encodedSTRING value into aBYTES value.
For more information, seeString functions.
INT64 Converts a JSON number to a SQLINT64 value.
For more information, seeJSON functions.
INT64_ARRAY Converts a JSON array of numbers to a SQLARRAY<INT64> value.
For more information, seeJSON functions.
LAX_BOOL Attempts to convert a JSON value to a SQLBOOL value.
For more information, seeJSON functions.
LAX_FLOAT64 Attempts to convert a JSON value to a SQLFLOAT64 value.
For more information, seeJSON functions.
LAX_INT64 Attempts to convert a JSON value to a SQLINT64 value.
For more information, seeJSON functions.
LAX_STRING Attempts to convert a JSON value to a SQLSTRING value.
For more information, seeJSON functions.
PARSE_DATE Converts aSTRING value to aDATE value.
For more information, seeDate functions.
PARSE_JSON Converts a JSON-formattedSTRING value to aJSON value.
For more information, seeJSON functions.
PARSE_TIMESTAMP Converts aSTRING value to aTIMESTAMP value.
For more information, seeTimestamp functions.
SAFE_CAST Similar to theCAST function, but returnsNULL when a runtime error is produced.
SAFE_CONVERT_BYTES_TO_STRING Converts aBYTES value to aSTRING value and replace any invalid UTF-8 characters with the Unicode replacement character,U+FFFD.
For more information, seeString functions.
STRING (JSON) Converts a JSON string to a SQLSTRING value.
For more information, seeJSON functions.
STRING_ARRAY Converts a JSON array of strings to a SQLARRAY<STRING> value.
For more information, seeJSON functions.
STRING (Timestamp) Converts aTIMESTAMP value to aSTRING value.
For more information, seeTimestamp functions.
TIMESTAMP_MICROS Converts the number of microseconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP.
For more information, seeTimestamp functions.
TIMESTAMP_MILLIS Converts the number of milliseconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP.
For more information, seeTimestamp functions.
TIMESTAMP_SECONDS Converts the number of seconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP.
For more information, seeTimestamp functions.
TO_BASE32 Converts aBYTES value to a base32-encodedSTRING value.
For more information, seeString functions.
TO_BASE64 Converts aBYTES value to a base64-encodedSTRING value.
For more information, seeString functions.
TO_CODE_POINTS Converts aSTRING orBYTES value into an array of extended ASCII code points.
For more information, seeString functions.
TO_HEX Converts aBYTES value to a hexadecimalSTRING value.
For more information, seeString functions.
TO_JSON Converts a SQL value to a JSON value.
For more information, seeJSON functions.
TO_JSON_STRING Converts aJSON value to a SQL JSON-formattedSTRING value.
For more information, seeJSON functions.
UNIX_DATE Converts aDATE value to the number of days since 1970-01-01.
For more information, seeDate functions.
UNIX_MICROS Converts aTIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC.
For more information, seeTimestamp functions.
UNIX_MILLIS Converts aTIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC.
For more information, seeTimestamp functions.
UNIX_SECONDS Converts aTIMESTAMP value to the number of seconds since 1970-01-01 00:00:00 UTC.
For more information, seeTimestamp functions.

CAST

CAST(expressionAStypename)

Description

Cast syntax is used in a query to indicate that the result type of anexpression should be converted to some other type.

When usingCAST, a query can fail if GoogleSQL is unable to performthe cast. If you want to protect your queries from these types of errors, youcan useSAFE_CAST.

Casts between supported types that don't successfully map from the originalvalue to the target domain produce runtime errors. For example, castingBYTES toSTRING where the byte sequence isn't valid UTF-8 results in aruntime error.

Examples

The following query results in"true" ifx is1,"false" for any othernon-NULL value, andNULL ifx isNULL.

CAST(x=1ASSTRING)

CAST AS ARRAY

CAST(expressionASARRAY<element_type>)

Description

GoogleSQL supportscasting toARRAY. Theexpression parameter can represent an expression for these data types:

  • ARRAY

Conversion rules

FromToRule(s) when castingx
ARRAYARRAY Must be the exact same array type.

CAST AS BOOL

CAST(expressionASBOOL)

Description

GoogleSQL supportscasting toBOOL. Theexpression parameter can represent an expression for these data types:

  • INT64
  • BOOL
  • STRING

Conversion rules

FromToRule(s) when castingx
INT64BOOL ReturnsFALSE ifx is0,TRUE otherwise.
STRINGBOOL ReturnsTRUE ifx is"true" andFALSE ifx is"false"
All other values ofx are invalid and throw an error instead of casting to a boolean.
A string is case-insensitive when converting to a boolean.

CAST AS BYTES

CAST(expressionASBYTES)

Description

GoogleSQL supportscasting toBYTES. Theexpression parameter can represent an expression for these data types:

  • BYTES
  • STRING
  • PROTO

Conversion rules

FromToRule(s) when castingx
STRINGBYTES 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.
PROTOBYTES Returns the proto2 wire format bytes ofx.

CAST AS DATE

CAST(expressionASDATE)

Description

GoogleSQL supportscasting toDATE. Theexpressionparameter can represent an expression for these data types:

  • STRING
  • TIMESTAMP

Conversion rules

FromToRule(s) when castingx
STRINGDATE 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's outside of the supported min/max range, then an error is produced.
TIMESTAMPDATE Casting from a timestamp to date effectively truncates the timestamp as of the default time zone.

CAST AS ENUM

CAST(expressionASENUM)

Description

GoogleSQL supportscasting toENUM. Theexpressionparameter can represent an expression for these data types:

  • INT64
  • STRING
  • ENUM

Conversion rules

FromToRule(s) when castingx
ENUMENUMMust have the same enum name.

CAST AS Floating Point

CAST(expressionASFLOAT64)
CAST(expressionASFLOAT32)

Description

GoogleSQL supportscasting to floating point types.Theexpression parameter can represent an expression for these data types:

  • INT64
  • FLOAT32
  • FLOAT64
  • NUMERIC
  • STRING

Conversion rules

FromToRule(s) when castingx
INT64Floating Point Returns a close but potentially not exact floating point value.
NUMERICFloating PointNUMERIC will convert to the closest floating point number with a possible loss of precision.
STRINGFloating Point Returnsx as a floating point value, interpreting it as having the same form as a valid floating point literal. Also supports casts from"[+,-]inf" to[,-]Infinity,"[+,-]infinity" to[,-]Infinity, and"[+,-]nan" toNaN. Conversions are case-insensitive.

CAST AS INT64

CAST(expressionASINT64)

Description

GoogleSQL supportscasting to integer types.Theexpression parameter can represent an expression for these data types:

  • INT64
  • FLOAT32
  • FLOAT64
  • NUMERIC
  • ENUM
  • BOOL
  • STRING

Conversion rules

FromToRule(s) when castingx
Floating Point INT64 Returns the closest integer value.
Halfway cases such as 1.5 or -0.5 round away from zero.
BOOLINT64 Returns1 ifx isTRUE,0 otherwise.
STRINGINT64 A hex string can be cast to an integer. For example,0x123 to291 or-0x123 to-291.

Examples

If you are working with hex strings (0x123), you can cast those strings asintegers:

SELECT'0x123'ashex_value,CAST('0x123'asINT64)ashex_to_int;/*-----------+------------+ | hex_value | hex_to_int | +-----------+------------+ | 0x123     | 291        | +-----------+------------*/
SELECT'-0x123'ashex_value,CAST('-0x123'asINT64)ashex_to_int;/*-----------+------------+ | hex_value | hex_to_int | +-----------+------------+ | -0x123    | -291       | +-----------+------------*/

CAST AS INTERVAL

CAST(expressionASINTERVAL)

Description

GoogleSQL supportscasting toINTERVAL. Theexpression parameter can represent an expression for these data types:

  • STRING

Conversion rules

FromToRule(s) when castingx
STRINGINTERVAL When casting from string to interval, the string must conform to eitherISO 8601 Duration standard or to interval literal format 'Y-M D H:M:S.F'. Partial interval literal formats are also accepted when they aren't ambiguous, for example 'H:M:S'. If the string expression is invalid or represents an interval that is outside of the supported min/max range, then an error is produced.

Examples

SELECTinput,CAST(inputASINTERVAL)ASoutputFROMUNNEST(['1-2 3 10:20:30.456','1-2','10:20:30','P1Y2M3D','PT10H20M30,456S'])input/*--------------------+--------------------+ | input              | output             | +--------------------+--------------------+ | 1-2 3 10:20:30.456 | 1-2 3 10:20:30.456 | | 1-2                | 1-2 0 0:0:0        | | 10:20:30           | 0-0 0 10:20:30     | | P1Y2M3D            | 1-2 3 0:0:0        | | PT10H20M30,456S    | 0-0 0 10:20:30.456 | +--------------------+--------------------*/

CAST AS NUMERIC

CAST(expressionASNUMERIC)

Description

GoogleSQL supportscasting toNUMERIC. Theexpression parameter can represent an expression for these data types:

  • INT64
  • FLOAT32
  • FLOAT64
  • NUMERIC
  • STRING

Conversion rules

FromToRule(s) when castingx
Floating PointNUMERIC The floating point number will roundhalf away from zero. Casting aNaN,+inf or-inf will return an error. Casting a value outside the range ofNUMERIC returns an overflow error.
STRINGNUMERIC The numeric literal contained in the string must not exceed the maximum precision or range of theNUMERIC type, or an error will occur. If the number of digits after the decimal point exceeds nine, then the resultingNUMERIC value will roundhalf away from zero. to have nine digits after the decimal point.

CAST AS PROTO

CAST(expressionASPROTO)

Description

GoogleSQL supportscasting toPROTO. Theexpression parameter can represent an expression for these data types:

  • STRING
  • BYTES
  • PROTO

Conversion rules

FromToRule(s) when castingx
STRINGPROTO Returns the protocol buffer that results from parsing from proto2 text format.
Throws an error if parsing fails, e.g., if not all required fields are set.
BYTESPROTO Returns the protocol buffer that results from parsingx from the proto2 wire format.
Throws an error if parsing fails, e.g., if not all required fields are set.
PROTOPROTOMust have the same protocol buffer name.

Example

This example references a protocol buffer calledAward.

messageAward{requiredint32year=1;optionalint32month=2;repeatedTypetype=3;messageType{optionalstringaward_name=1;optionalstringcategory=2;}}
SELECTCAST('''    year: 2001    month: 9    type { award_name: 'BestArtist' category: 'Artist' }    type { award_name: 'BestAlbum' category: 'Album' }'''ASgooglesql.examples.music.Award)ASaward_col/*---------------------------------------------------------+ | award_col                                               | +---------------------------------------------------------+ | {                                                       | |   year: 2001                                            | |   month: 9                                              | |   type { award_name: "Best Artist" category: "Artist" } | |   type { award_name: "Best Album" category: "Album" }   | | }                                                       | +---------------------------------------------------------*/

CAST AS STRING

CAST(expressionASSTRING)

Description

GoogleSQL supportscasting toSTRING. Theexpression parameter can represent an expression for these data types:

  • INT64
  • FLOAT32
  • FLOAT64
  • NUMERIC
  • ENUM
  • BOOL
  • BYTES
  • PROTO
  • DATE
  • TIMESTAMP
  • INTERVAL
  • STRING

Conversion rules

FromToRule(s) when castingx
Floating PointSTRINGReturns an approximate string representation. A returnedNaN or0 will not be signed.
BOOLSTRING Returns"true" ifx isTRUE,"false" otherwise.
BYTESSTRING Returnsx interpreted as a UTF-8 string.
For example, the bytes literalb'\xc2\xa9', when cast to a string, is interpreted as UTF-8 and becomes the unicode character "©".
An error occurs ifx isn't valid UTF-8.
ENUMSTRING Returns the canonical enum value name ofx.
If an enum value has multiple names (aliases), the canonical name/alias for that value is used.
PROTOSTRINGReturns the proto2 text format representation ofx.
DATESTRING Casting from a date type to a string is independent of time zone and is of the formYYYY-MM-DD.
TIMESTAMPSTRING When casting from timestamp types to string, the timestamp is interpreted using the default time zone, America/Los_Angeles. 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.
INTERVALSTRING Casting from an interval to a string is of the formY-M D H:M:S.

Examples

SELECTCAST(CURRENT_DATE()ASSTRING)AScurrent_date/*---------------+ | current_date  | +---------------+ | 2021-03-09    | +---------------*/
SELECTCAST(INTERVAL3DAYASSTRING)ASinterval_to_string/*--------------------+ | interval_to_string | +--------------------+ | 0-0 3 0:0:0        | +--------------------*/
SELECTCAST(INTERVAL"1-2 3 4:5:6.789"YEARTOSECONDASSTRING)ASinterval_to_string/*--------------------+ | interval_to_string | +--------------------+ | 1-2 3 4:5:6.789    | +--------------------*/

CAST AS STRUCT

CAST(expressionASSTRUCT)

Description

GoogleSQL supportscasting toSTRUCT. Theexpression parameter can represent an expression for these data types:

  • STRUCT

Conversion rules

FromToRule(s) when castingx
STRUCTSTRUCT Allowed if the following conditions are met:
  1. The two structs have the same number of fields.
  2. The original struct field types can be explicitly cast to the corresponding target struct field types (as defined by field order, not field name).

CAST AS TIMESTAMP

CAST(expressionASTIMESTAMP)

Description

GoogleSQL supportscasting toTIMESTAMP. Theexpression parameter can represent an expression for these data types:

  • STRING
  • TIMESTAMP

Conversion rules

FromToRule(s) when castingx
STRINGTIMESTAMP When casting from string to a timestamp,string_expression must conform to the supported timestamp literal formats, or else a runtime error occurs. Thestring_expression may itself contain a time zone.

If there is a time zone in thestring_expression, that time zone is used for conversion, otherwise the default time zone, America/Los_Angeles, is used. If the string has fewer than six digits, then it's implicitly widened.

An error is produced if thestring_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.
DATETIMESTAMP Casting from a date to a timestamp interpretsdate_expression as of midnight (start of the day) in the default time zone, America/Los_Angeles.

Examples

The following example casts a string-formatted timestamp as a timestamp:

SELECTCAST("2020-06-02 17:00:53.110+00:00"ASTIMESTAMP)ASas_timestamp-- Results depend upon where this query was executed./*-------------------------+ | as_timestamp            | +-------------------------+ | 2020-06-03T00:00:53.11Z | +-------------------------*/

SAFE_CAST

SAFE_CAST(expressionAStypename)

Description

When usingCAST, a query can fail if GoogleSQL is unable to performthe cast. For example, the following query generates an error:

SELECTCAST("apple"ASINT64)ASnot_a_number;

If you want to protect your queries from these types of errors, you can useSAFE_CAST.SAFE_CAST replaces runtime errors withNULLs. However, duringstatic analysis, impossible casts between two non-castable types still producean error because the query is invalid.

SELECTSAFE_CAST("apple"ASINT64)ASnot_a_number;/*--------------+ | not_a_number | +--------------+ | NULL         | +--------------*/

If you are casting from bytes to strings, you can also use thefunction,SAFE_CONVERT_BYTES_TO_STRING. Any invalid UTF-8 charactersare replaced with the unicode replacement character,U+FFFD.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.