SQL Server to Databricks: function mapping
6 of 12 common SQL Server functions need a change in Databricks. Mapping table with Databricks syntax and behavior differences.
Other targets: SQL Server to Snowflake · SQL Server to BigQuery
Needs a rewrite (3)
No Databricks function with the same behavior; use the construct shown.
| SQL Server | Databricks | Notes |
|---|---|---|
| CONVERT | 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. 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. |
| EOMONTH | Rewrite last_day(expr) | Returns the last day of the month as a DATE. 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. |
| TOP | Rewrite SELECT ... LIMIT n | LIMIT goes at the end of the query. 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 | Databricks | Notes |
|---|---|---|
| IIF | Different name iff(cond, expr1, expr2) | Synonym of if(cond, expr1, expr2). 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(expr1, expr2) | nvl(expr1, expr2) is a synonym. Both are documented as coalesce with two arguments. The built-in isnull(expr) only returns true or false. 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. A query that keeps ISNULL(col, 0) fails on Databricks because isnull takes one argument. A query that uses ISNULL(col) as a condition still runs but means something else. |
| STUFF | Different name overlay(input PLACING replace FROM pos FOR len) | Also callable as overlay(input, replace, pos, len); note that replace comes before pos. 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. |
Works as written (6)
The same call runs, but check the notes for behavior differences.
| SQL Server | Databricks | Notes |
|---|---|---|
| CHARINDEX | Same name charindex(substr, str [, pos]) | Synonym of locate; 1-based, returns 0 when not found. 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. |
| DATEADD | Same name dateadd(unit, value, expr) | Synonym of timestampadd, available in Databricks Runtime 10.4 LTS and above. Returns a TIMESTAMP. The two-argument dateadd(startDate, numDays) form adds days to a DATE. 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. The three-argument dateadd returns a TIMESTAMP even for DATE input; cast back with CAST(... AS DATE) if a date column is expected. |
| DATEDIFF | Same name datediff(unit, start, end) | Synonym of timestampdiff (Databricks Runtime 10.4 LTS and above). Counts whole elapsed units: one month has passed only when the day and time reach the start value. The two-argument datediff(endDate, startDate) form returns days and takes the end date first. Databricks documents that datediff(MONTH, TIMESTAMP '2021-02-28 12:00:00', TIMESTAMP '2021-03-28 11:59:59') returns 0 because a full month has not elapsed, while SQL Server returns 1 because a month boundary was crossed. Month, quarter and year differences are the most common source of mismatched reports after migration. datediff(a, b) with two arguments is end minus start in days, the reverse of the three-argument form. |
| GETDATE | Same name getdate() | Synonym of current_timestamp(). Evaluated once at the start of the query. 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. |
| LEN | Same name len(expr) | Synonym of char_length (Databricks Runtime 11.3 LTS and above). Counts trailing spaces. 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. |
| STRING_AGG | Same name string_agg([DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Databricks SQL and Databricks Runtime 16.4 and later; alias of listagg. On older runtimes use array_join(collect_list(expr), delimiter), which does not guarantee order. The separator is optional in the targets and defaults to an empty string, while SQL Server requires it. Keep it explicit to avoid values running together. 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. |