CAST

​

Syntax

​

Scalar

cast(value AS target_type)

​

Arguments

​

Returns

• Type: Requested target SQL type
• NULL behavior: A NULL value remains NULL for a supported conversion. Invalid string-to-numeric values currently produce NULL even when ANSI mode is enabled.
• Shape: For plain CAST to ARRAY/MAP/STRUCT, the input is passed through as an already-shaped value; TRY_CAST does not implement that complex-type annotation shortcut.
• Ordering: Plain CAST does not reorder elements in its complex-type annotation path.

​

Properties

​

Usage notes

• Write the target type directly in the call. It cannot be a STRING expression that changes for each row.
• String-to-integer conversion first parses as DOUBLE and then truncates toward zero.
• String-to-DATE and plain STRING-to-TIMESTAMP paths use ANSI-aware parsing: invalid non-NULL values can raise errors in ANSI mode and return NULL otherwise.
• Binary-to-string conversion replaces invalid UTF-8 bytes with U+FFFD.
• Casting a ROW, MAP, or ARRAY changes its declared type without converting the contained values.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Not every source type can be converted to every target type. MAP-to-STRING and STRUCT-to-STRING conversions are rejected. Casting ARRAY, MAP, or STRUCT does not recursively convert its elements or fields.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT CAST('42' AS INT) AS "value", CAST(CAST(NULL AS STRING) AS INT) AS null_value;

​

Convert text to decimal and date

WITH sample AS (
  SELECT id, CASE WHEN id = 0 THEN '12.50' ELSE '7.25' END AS price_text,
    CASE WHEN id = 0 THEN '2024-03-15' ELSE '2024-03-16' END AS date_text
  FROM range(0, 2)
)
SELECT CAST(price_text AS DECIMAL(8,2)) AS price, CAST(date_text AS DATE) AS order_date FROM sample ORDER BY id;

​

Limitations

• Unsupported conversions or incomplete target-type definitions can cause the query to be rejected before execution. TRY_CAST does not turn those errors into NULL.
• CHAR and VARCHAR length limits are not enforced by this cast. Do not rely on the cast to truncate text.
• Precision loss and error handling depend on the input type, target type, ANSI mode, and conversion rules described above.
• VARIANT-to-scalar conversions follow VARIANT_GET’s rules. When converting to a narrower integer type, do not rely on Spark-style wraparound behavior.

​

Related functions

• typeof
• try_cast