Every distinct Date between DateA and Date B -TSQL

I’m searching for a query like a calendar giving me back the distinct Dates between "Date A" and Date "A -49 days".

Date A is the a variable. If I look on the Query on Monday to Sunday it will give me back

1. the Date of the Sunday in the previous Week
2. the Date of the Sunday in the Week before the previous week
3. 2 Weeks before the Previous Week
4. 5 Weeks before the Previous Week

For Example: I started the query in ‘2022-01-23’

a_end: ‘2022-01-16’ a_beginn: ‘2021-12-05′ and every date between
b_end:’2022-01-09’ b_beginn: ‘2021-11-29’ and every date between
etc.

>Solution :

You could use a recursive CTE :

WITH T(d) AS (
  SELECT CAST('2022-01-01' AS date)
  UNION ALL
  SELECT DATEADD(day, -1, d)
  FROM T
  WHERE d >= DATEADD(day, -49, '2022-01-01')
)
SELECT d
FROM T
-- OPTION (MAXRECURSION 1000)

If you have more than 100 days to generate you will need to set the MAXRECURSION query hint which is limited to 100 by default (0 means no limit). Beware of infinite loops with this setting though.