SQL Server CONVERT in Snowflake, Databricks and BigQuery
CONVERT(type, expr) without a style is a plain CAST in every target. With a style code such as 112 or 120, rewrite it as a format function: TO_CHAR in Snowflake, date_format in Databricks and FORMAT_DATE or FORMAT_DATETIME in BigQuery.
SQL Server function: CONVERT(data_type [(length)], expression [, style]) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | CAST(expr AS type) / TO_CHAR(expr, 'YYYYMMDD') | Style 112 (yyyyMMdd) is 'YYYYMMDD', style 23 (yyyy-MM-dd) is 'YYYY-MM-DD', style 120 is 'YYYY-MM-DD HH24:MI:SS'. | Snowflake docs |
| Databricks | Rewrite | CAST(expr AS type) / date_format(expr, 'yyyyMMdd') | date_format uses Java-style datetime patterns: style 120 becomes 'yyyy-MM-dd HH:mm:ss'. Note MM is month and mm is minute. | Databricks docs |
| BigQuery | Rewrite | CAST(expr AS type) / FORMAT_DATE('%Y%m%d', expr) | Format strings use % elements: style 120 becomes '%Y-%m-%d %H:%M:%S' with FORMAT_DATETIME. | BigQuery docs |
Behavior differences to check
- Style codes only exist in SQL Server. Every CONVERT with a third argument needs a manual rewrite; a search for "CONVERT(" with three arguments finds them.
- CONVERT(DATE, expr) on strings relies on SQL Server language settings to read the input. In the targets, parse strings with TO_DATE (Snowflake, Databricks) or PARSE_DATE (BigQuery) and an explicit format.
Examples
Date as yyyyMMdd text (style 112)
SELECT CONVERT(VARCHAR(8), order_date, 112) AS order_day FROM orders;SELECT TO_CHAR(order_date, 'YYYYMMDD') AS order_day FROM orders;SELECT date_format(order_date, 'yyyyMMdd') AS order_day FROM orders;SELECT FORMAT_DATE('%Y%m%d', order_date) AS order_day FROM orders;