SQL Server to BigQuery: function mapping
11 of 12 common SQL Server functions need a change in BigQuery. Mapping table with BigQuery syntax and behavior differences.
Other targets: SQL Server to Snowflake · SQL Server to Databricks
Needs a rewrite (8)
No BigQuery function with the same behavior; use the construct shown.
| SQL Server | BigQuery | Notes |
|---|---|---|
| CHARINDEX | Rewrite STRPOS(value, subvalue) | Arguments reversed: the string to search comes first. Use INSTR(value, subvalue, position) for a start position. Keeping the SQL Server argument order in STRPOS searches for the column inside the literal and almost always returns 0, without an error. SQL Server comparisons follow the column collation, which is often case-insensitive. The targets compare case-sensitively by default, so CHARINDEX('abc', 'ABC') stops matching. |
| CONVERT | 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. 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. |
| DATEADD | Rewrite DATE_ADD(date_expression, INTERVAL int64 date_part) | Pick the function by input type: DATE_ADD for DATE, DATETIME_ADD for DATETIME, TIMESTAMP_ADD for TIMESTAMP. Month arithmetic at month end: SQL Server returns the last day of the target month when the day does not exist (Jan 31 + 1 month = Feb 28 or 29). Snowflake DATEADD, Databricks and BigQuery behave the same way for this case. Units are keywords, not strings: INTERVAL 7 DAY, not INTERVAL '7' 'day'. Abbreviations such as dd are not accepted. |
| DATEDIFF | Rewrite DATE_DIFF(end_date, start_date, granularity) | End date first. Counts unit boundaries, like SQL Server. Use DATETIME_DIFF or TIMESTAMP_DIFF for other types. Swapping the arguments changes the sign. DATE_DIFF(start, end, DAY) silently returns a negative number. |
| EOMONTH | Rewrite LAST_DAY(date_expression [, date_part]) | Returns a DATE. Also accepts DATETIME with DATETIME-specific parts. EOMONTH(d, n) has no single-function equivalent. Write LAST_DAY(DATEADD(month, n, d)) in Snowflake, last_day(add_months(d, n)) in Databricks and LAST_DAY(DATE_ADD(d, INTERVAL n MONTH)) in BigQuery. |
| GETDATE | Rewrite CURRENT_DATETIME([time_zone]) | Returns DATETIME without a time zone, in UTC unless a time zone is passed. CURRENT_TIMESTAMP() returns a TIMESTAMP (an absolute point in time). SQL Server GETDATE returns the local time of the database server as DATETIME with no time zone. Cloud warehouses default to UTC or the session time zone, so day boundaries and "today" filters can shift by several hours. Set the session time zone or pass one explicitly. Use CURRENT_DATETIME('America/New_York') (or your server's zone) to reproduce local wall-clock time. |
| STUFF | Rewrite CONCAT(SUBSTR(s, 1, pos - 1), replacement, SUBSTR(s, pos + len)) | No STUFF or INSERT string function. The common pattern STUFF((SELECT ',' + name FROM ... FOR XML PATH('')), 1, 1, '') only removes the leading comma of an XML-built list. Replace the whole expression with LISTAGG (Snowflake), string_agg or listagg (Databricks) or STRING_AGG (BigQuery). See STRING_AGG. SQL Server STUFF returns NULL when start is larger than the string length. Check how each target handles positions past the end before relying on it. |
| TOP | Rewrite SELECT ... LIMIT n | LIMIT goes at the end of the query; OFFSET is optional. TOP n PERCENT and TOP n WITH TIES need a rewrite with window functions, for example QUALIFY RANK() OVER (ORDER BY score DESC) <= n for WITH TIES (QUALIFY works in all three targets). TOP in UPDATE and DELETE statements is SQL Server only; rewrite those with a key filter. |
Different name (3)
Same behavior under another name; rename the call and check the notes.
| SQL Server | BigQuery | Notes |
|---|---|---|
| IIF | Different name IF(expr, true_result, else_result) | Returns else_result when expr is FALSE or NULL. A NULL condition returns the false branch in all three targets, the same as SQL Server. Nested IIF calls translate one to one, but a CASE expression is easier to read when there are more than two branches. |
| ISNULL | Different name IFNULL(expr, null_result) | COALESCE(expr, null_result) is equivalent. Return type: SQL Server returns the type of the first argument and may truncate the replacement, for example ISNULL(CAST(NULL AS VARCHAR(2)), 'abc') returns 'ab'. The target functions return a common type of both arguments, so results that relied on truncation change. |
| LEN | Different name LENGTH(value) | Counts characters for STRING and bytes for BYTES. SQL Server LEN('abc ') returns 3; the targets return 5. Wrap the argument in RTRIM to keep the old result, for example LENGTH(RTRIM(col)). Columns migrated from CHAR(n) may arrive padded with spaces, which makes the difference more common than expected. |
Works as written (1)
The same call runs, but check the notes for behavior differences.
| SQL Server | BigQuery | Notes |
|---|---|---|
| STRING_AGG | Same name STRING_AGG([DISTINCT] expr [, delimiter] [ORDER BY ...] [LIMIT n]) | ORDER BY goes inside the parentheses. The default delimiter is a comma. WITHIN GROUP is not accepted; move the ORDER BY inside STRING_AGG(...). SQL Server returns an error when the result exceeds 8,000 bytes for VARCHAR input unless the input is cast to VARCHAR(MAX). The targets have their own size limits but no such cast is needed. |