Converting SQL Query to Databricks SQL

Question

I have a query that I need to convert to Databricks SQL or run against a table in a Databrick environment but failing even though it works very well against tables SQL Server. The tables and query can be found here

The query to convert or run in Databricks environment is:

;WITH CTE AS (
    SELECT EventID
         , EventName
         , EventStartDateTime
         , IIF(EventEndDateTime = '',GETUTCDATE(),EventEndDateTime) 
           AS EventEndDateTime
     FROM EventLog

     UNION ALL

     SELECT EventID
          , EventName
          , DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , 
             EventStartDateTime)), 0) AS EventStartDateTime
          , EventEndDateTime
     FROM CTE 
     WHERE  DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , 
     EventStartDateTime)), 0)  <= EventEndDateTime
  )

And then selecting from the CTE like so:

SELECT EventID
     , EventName
     , YEAR(EventStartDateTime)
     , DATENAME(MONTH,EventStartDateTime)
     , DATEDIFF(SECOND, EventStartDateTime, n_EventStartDateTime) / 
         3600.0
 FROM (
   SELECT EventID
        , EventName
        , EventStartDateTime
        , LEAD(EventStartDateTime,1,EventEndDateTime) 
          OVER(PARTITION BY EventID,EventName ORDER BY 
           EventStartDateTime) n_EventStartDateTime
   FROM CTE
  ) t1
      OPTION (maxrecursion 0)

I equally noticed that

DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , 
          EventStartDateTime)), 0) AS EventStartDateTime

and

DATEDIFF(SECOND, EventStartDateTime, n_EventStartDateTime) / 3600.0

are not working in databricks environment.

I sincerely appreciate your help on this

Answer 1

According to the error you found, the particular codes should be modified as follows:

SQL Server Version:

DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , 
          EventStartDateTime)), 0) AS EventStartDateTime

Databricks Version:

add_months('1900-01-01', months_between(add_months(EventStartDateTime, 1), '1900-01-01'))

SQL Server Version:

DATEDIFF(SECOND, EventStartDateTime, n_EventStartDateTime) / 3600.0

Databricks Version:

datediff(EventStartDateTime, n_EventStartDateTime) * 24

Read more about Databricks date function. Note that some functions are just released after particular Databricks SQL endpoint versions which may not be available yet.