databricks-sql

Databricks function DATE_ADD(DAY, 7, t.openDate) Not recognizing DAY clause

I have reformatted a T-SQL DATEADD function to Databricks date_add function. However, I'm getting the error that Databricks doesn't recognize the 'DAY' clause with the following error:

cannot resolve '`DAY`

The full code is DATE_ADD(DAY, 7, t.openDate)

The T-SQL version is DATEADD(DAY, 7, t.openDate)

Can someone let me know what the equivalent clause is for 'DAY' in Databricks SQL?

Solution

  • Databricks has both a DATE_ADD and a DATEADD function. The former only takes two arguments, and assumes you want to add days. The latter works the same as the T-SQL version. See https://docs.databricks.com/en/sql/language-manual/functions/date_add.html and https://docs.databricks.com/en/sql/language-manual/functions/dateadd.html.

    So in your case you could use either

    DATE_ADD(7, t.openDate)

    or

    DATEADD(DAY, 7, t.openDate)