Spark sql DATEADD

Question

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?

Solution

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)