[SOLVED] I want to create table or view from a recursion used to generate a date in ssms

I want to create table or view from a recursion used to generate a date in ssms

I wrote a recursive query to generate a column pf dates. I want the dates to be stored as a table in a db but can't seem to find a way.

declare @startdate date = '2014-01-01';
declare @enddate date = '2023-12-31';
with calendar as
 (
 select @startdate as [orderDate]
 union all
 select DATEADD(dd,1,[orderdate])
 from calendar
 where DATEADD(dd,1,[orderdate])<= @enddate
 )
 select * from calendar
 option (maxrecursion 0);

Solution

you can try this one to fill a new table your_table with the dates. You can use that as a basis for your further operations.

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))

select 
convert(date, dat ) Dat
into your_table
from 
(
    SELECT top 100 percent
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Line,
    dateadd(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2014-01-01') Dat
    FROM x ones, x tens, x hundreds, x thousands
    ORDER BY 1
) basis
where dat <= '2023-12-31'