SQL Server - recursive DATEADD query

Question

How do I get the following query give me the same date for the last N years?

declare @step int = 0;
declare @datobserve date = '2021-11-03';

with dates as 
(
select dateadd(year, @step, @datobserve) datobserve, @step step
union all
select dateadd(year, step, datobserve) as datobserve, step - 1
from dates
where 1=1
--and step = step + 1
and step > -4
)
select * from dates

The result I am getting is:

instead of:

Answer 1

Just another option using an ad-hoc tally table

Example

Declare @Years int = 4
Declare @Date date ='2021-11-03'

Select dateobserve = dateadd(year,N,@Date)
      ,Step = N
 From ( Select Top (@Years+1) N=1-Row_Number() Over (Order By (Select NULL)) 
          From master..spt_values n1
      ) NT

Results

dateobserve Step
2021-11-03  0
2020-11-03  -1
2019-11-03  -2
2018-11-03  -3
2017-11-03  -4

Answer 2

If you want to find the errors in your ... recursive DATEADD query ... and get the ... same date for the last N years ..., you need to make two changes in the recursive member of your statement:

• Use @datobserve instead of datobserve
• Use step - 1 instead of step

Statement:

declare @step int = 0;
declare @n int = 4;
declare @datobserve date = '2021-11-03';

with dates as 
(
   select dateadd(year, @step, @datobserve) datobserve, @step step
   union all
   select dateadd(year, step - 1, @datobserve) as datobserve, step - 1
   from dates
   where step > -@n
)
select * 
from dates
option (MAXRECURSION 0);

Result:

datobserve  step
2021-11-03  0
2020-11-03  -1
2019-11-03  -2
2018-11-03  -3
2017-11-03  -4

Answer 3

When you know it's only a handful of rows, like 5, I find it simpler to not bother with recursion:

DECLARE @datobserve date = '20211103';

;WITH n(n) AS 
(
  SELECT n FROM (VALUES(1),(2),(3),(4),(5)) AS n(n)
)
SELECT datobserve = DATEADD(YEAR, 1-n, @datobserve), step = 1-n 
FROM n
ORDER BY datobserve DESC;

If the list is larger or variable, I still like to get the numbers part recursively, and work the dates around that output:

DECLARE @steps      int  = 5,
        @datobserve date = '20211103';

;WITH n(n) AS 
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < @steps
)
SELECT datobserve = DATEADD(YEAR, 1-n, @datobserve), step = 1-n 
FROM n
ORDER BY datobserve DESC;

• Examples @ db<>fiddle