Spark SQL, for example in Databricks, is different to T-SQL in Microsoft SQL Server. Below are some examples of the differences, considering dates.
In general, Spark SQL and T-SQL are relatively similar, date queries are the most common difference. Window functions are the same in Spark SQL. UNPIVOT AND PIVOT are different in Spark SQL to T-SQL.
Differences in date queries between Spark SQL and T-SQL
| Spark SQL | T-SQL |
| current_date() | cast(getdate() as date) |
| current_timestamp() | getdate() |
| date_add(current_date(),-5) –two parameter | dateadd(day,-5,getdate()) –-three parameter |
| add_months(current_date(),12) –two parameter | dateadd(month,12,getdate()) –-three parameter |
| date_trunc(‘MONTH’,current_date()) | DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) |
| date(‘2100-01-01’) | DATEFROMPARTS(2100,1,1) |
| cast(null as timestamp) –alternatives string / double | null |
| dayofweek() | DATEPART(weekday,getdate()) |
| CAST(months_between(myenddate,mystartdate) as int) –first mentioned end date | DATEDIFF(month,mystartdate,myenddate) -–three parameter |
| ceil(datediff(myenddate,mystartdate)/7)) –two parameter, datediff only dates | DATEDIFF(week,mystartdate,myenddate) -–three parameter |
Similarities in date queries between Spark SQL and T-SQL
DAY(), MONTH(), YEAR() are the same.