WITH RECURSIVE dates(dt) AS
(
SELECT to_date('2023-03-05') -181 as dt
UNION ALL
SELECT dt + 1
FROM dates d
WHERE d.dt < to_date('2023-03-05')
)
WITH RECURSIVE dates(dt) AS
(
SELECT to_date('2023-03-05') -181 as dt
UNION ALL
SELECT dt + 1
FROM dates d
WHERE d.dt < to_date('2023-03-05')
)
Apache Spark doesn't support native recursive CTE syntax. You can track the status using the SPARK-24497 Jira.
Until it's implemented you will need to use some workarounds, like, described in the following article - where you can do a combination of normal CTE and unions.