sql - Add dates everyday to a specific date

Question

In SQL I want to take this startdate '2021-01-22' and every day add data of a currentdate to that start date. So my datatable will always keep the start date, but every day add the newest date.

Tried something like this in my stored procedure;

DECLARE @DATESTART DATE;
DECLARE @DATEEND DATE;
SET @DATESTART =   '2021-01-22';
SET @DATEEND =  (SELECT DATEADD(day, DATEDIFF(day, 1, @DATESTART ), 1));

question from:https://stackoverflow.com/questions/65841526/add-dates-everyday-to-a-specific-date

Answer 1

If you want to generate dates since a certain date, you can use a recursive CTE:

with dates as (
      select convert(date, '2020-01-01') as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < convert(date, getdate())
     )
select *
from dates
option (maxrecursion 0);

The results can be saved in a table. Or the CTE can be incorporated into another query.