how to generate a table of Monday dates that from a specific Monday to current date in sql

Question

In SQL Server I need to generate a table of Mondays up to the current date. The starting date is always 2020-04-27, which is a Monday. For example, if today is 2020-05-25, I need a table like below:

    date
0   2020-04-27
1   2020-05-04
2   2020-05-11
3   2019-05-18
4   2019-05-25

If today's date is 2020-05-23, then it's:

    date
0   2020-04-27
1   2020-05-04
2   2020-05-11
3   2019-05-18

How I can produce the table like that?

Answer 1

You can use a recursive CTE to generate the list of dates:

WITH mondays AS (
  SELECT CAST('2020-04-27' AS date) AS monday
  UNION ALL
  SELECT DATEADD(WEEK, 1, monday)
  FROM mondays
  WHERE DATEADD(WEEK, 1, monday) <= GETDATE()
)
SELECT *
FROM mondays

Output:

monday
2020-04-27
2020-05-04
2020-05-11
2020-05-18

Demo on dbfiddle

Note that if you want to generate a list of more than 100 dates, you will need to increase the maximum recursion level (which defaults to 100). This can be done by adding OPTION (MAXRECURSION 0) to the end of the query i.e.

SELECT *
FROM mondays
OPTION (MAXRECURSION 0)