-1

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);
Brad
  • 3,454
  • 3
  • 27
  • 50
oyelowo toyyib
  • 3
  • 1
  • So why can't you create a table and `insert` the rows from your CTE? – Stu Feb 06 '23 at 18:38
  • You can create the table ahead of time and just do an INSERT, or you can do select * INTO tablename (it will create the table on insert). This is not the best approach as the data types, columns, and indexes/etc wont be there when creating tables with SELECT * INTO. – Brad Feb 06 '23 at 18:43
  • SSMS is just an IDE-like application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? – Thom A Feb 06 '23 at 19:19

1 Answers1

0

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'
Christian Stenzel
  • 16
  • 2