SQL Server TRY_CAST in Snowflake, Databricks and BigQuery
Snowflake and Databricks both have TRY_CAST; BigQuery calls it SAFE_CAST. Snowflake only accepts string input, so TRY_CAST on a number or date column fails there.
SQL Server function: TRY_CAST(expression AS data_type [(length)]) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | TRY_CAST(source_string_expr AS target_data_type) | Only string expressions are accepted as input. | Snowflake docs |
| Databricks | Same name | try_cast(sourceExpr AS targetType) | Databricks SQL and Databricks Runtime 10.4 LTS and above. | Databricks docs |
| BigQuery | Different name | SAFE_CAST(expr AS type) | Returns NULL instead of an error. | BigQuery docs |
Behavior differences to check
- TRY_CONVERT with a style code (for example TRY_CONVERT(date, s, 103)) needs a parse function with an explicit format instead: TRY_TO_DATE in Snowflake, try_to_date in Databricks, SAFE.PARSE_DATE in BigQuery.
Snowflake
- TRY_CAST(amount AS INT) on a NUMBER column is an error in Snowflake. Cast the value to VARCHAR first, or use the TRY_TO_NUMBER and TRY_TO_DATE family.
Examples
Parse amounts stored as text
SELECT TRY_CAST(amount_text AS DECIMAL(12, 2)) AS amount FROM staging;SELECT TRY_CAST(amount_text AS DECIMAL(12, 2)) AS amount FROM staging;SELECT try_cast(amount_text AS DECIMAL(12, 2)) AS amount FROM staging;SELECT SAFE_CAST(amount_text AS NUMERIC) AS amount FROM staging;