I'm trying to filter out data from the current date to last 3 years and trying to use this for spark sql query: (eg : d_date column format 2009-09-18
)
WHERE d_date >= DATEADD(MONTH, -3, GETDATE())
But getting following error.
Undefined function: 'DATEADD'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.;
Is there any DATEADD equivalent for spark SQL?
Spark SQL has date_add function and it's different from the one you're trying to use as it takes only a number of days to add.
For your case you can use add_months to add -36 = 3 years
WHERE d_date >= add_months(current_date(), -36)
In addition to blackbishop's answer above, it's add_months if you're running Azure Data Bricks.
https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/add_months