Spark SQL vs T-SQL Date Query Differences

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

current_date()cast(getdate() as date)
date_add(current_date(),-5) –two parameterdateadd(day,-5,getdate()) –-three parameter
add_months(current_date(),12) –two parameterdateadd(month,12,getdate()) –-three parameter
date_trunc(‘MONTH’,current_date())DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
cast(null as timestamp) –alternatives string / doublenull
CAST(months_between(myenddate,mystartdate) as int) –first mentioned end dateDATEDIFF(month,mystartdate,myenddate) -–three parameter
ceil(datediff(myenddate,mystartdate)/7)) –two parameter, datediff only datesDATEDIFF(week,mystartdate,myenddate) -–three parameter

Similarities in date queries between Spark SQL and T-SQL

DAY(), MONTH(), YEAR() are the same.

Leave a Reply

Your email address will not be published. Required fields are marked *