Sql View with Cte
Posted on 2012-04-05
I need to generate a list of all the dates 14 days before current date to 90 days from current date.I have the following cte and I would like to create a view from it which I have to use it in access to link to other tables.
When it is less than 100 and when I exclude option part, I am able to create the view.
But when use option,
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range option (maxrecursion 150)
It gives me the following error while creating the view:
Incorrect syntax near the keyword 'option'.
Please let me know how to fix it.