Problem

You are attempting to use the date_add() or date_sub() functions in Spark 3.0, but they are returning an Error in SQL statement: AnalysisException error message.

In Spark 2.4 and below, both functions work as normal.

%sql

select date_add(cast('1964-05-23' as date), '12.34')

Cause

You are attempting to use a fractional or string value as the second argument.

In Spark 2.4 and below, if the second argument is a fractional or string value, it is coerced to an int value before date_add() or date_sub() is evaluated.

Using the example code listed above, the value 12.34 is converted to 12 before date_add() is evaluated.

In Spark 3.0, if the second argument is a fractional or string value, it returns an error.

Solution

Use int, smallint, or tinyint values as the second argument for the date_add() or date_sub() functions in Spark 3.0.

%sql

select date_add(cast('1964-05-23' as date), '12')

%sql

select date_add(cast('1964-05-23' as date), 12)

Both of these examples work properly in Spark 3.0.