Write an SQL query to generate a list of all dates of current month using Recursive CTE.

You are asked to generate a list of all dates using recursive CTEs. You cannot hardcode date in more than two places. Take the current date as input and generate list of dates of that month from start date to its end date. The dates should be in YYYY-MM-DD format with the title saying ‘List of Dates’.

This question was asked in Cognizant Data Analyst interview and the solution is available below along with the output.

SQL Query to generate a list of all dates of a month

WITH RECURSIVE date_list AS (
 
 SELECT DATE_TRUNC('month', DATE '2026-01-01')::DATE AS temp_date
 
 UNION ALL
 
 SELECT (temp_date+ INTERVAL '1 day')::DATE 
 FROM date_list
 WHERE temp_date < (DATE_TRUNC('month', DATE '2026-01-01') + INTERVAL '1 month')::DATE - INTERVAl '1 day'
)

SELECT temp_date AS "List Of Dates" FROM date_list;

WITH RECURSIVE date_list AS (
 
 SELECT DATE_TRUNC('month', DATE '2026-01-01')::DATE AS temp_date
 
 UNION ALL
 
 SELECT (temp_date+ INTERVAL '1 day')::DATE 
 FROM date_list
 WHERE temp_date < (DATE_TRUNC('month', DATE '2026-01-01') + INTERVAL '1 month')::DATE - INTERVAl '1 day'
)

SELECT temp_date AS "List Of Dates" FROM date_list;

Explanation:

• The query starts by finding the first day of the month using DATE_TRUNC, which for 2026-01-01 or any date from 2026-01-XX gives 2026-01-01 only. This is the anchor or the starting point.
• From there, the recursive part starts. One day is added to previous date, over and over, generating one new row each time.
• It stops by checking if the next date has crossed the last date of the month using WHERE logic
• Once that condition fails, recursion stops and all the generated dates are returned by the final SELECT as "List Of Dates"

Expected Output from the above query:

Row    List Of Dates
----------------------
1      2026-01-01
2      2026-01-02
3      2026-01-03
4      2026-01-04
5      2026-01-05
...
27     2026-01-27
28     2026-01-28
29     2026-01-29
30     2026-01-30
31     2026-01-31

Total rows: 31

Row    List Of Dates
----------------------
1      2026-01-01
2      2026-01-02
3      2026-01-03
4      2026-01-04
5      2026-01-05
...
27     2026-01-27
28     2026-01-28
29     2026-01-29
30     2026-01-30
31     2026-01-31

Total rows: 31

Follow up questions from the interviewer

• What is the difference between CTE and Recursive CTE?
  • CTE runs once, Recursive CTE references itself and repeats until a condition stops it.
• What is the anchor member and recursive member?
  • Anchor is the starting query, recursive member is the part that repeats by referencing the CTE itself.
• What happens if you forget the WHERE clause in a recursive CTE?
  • It runs infinitely and throws a max recursion error.
• What is the difference between DATE_TRUNC and DATE_PART?
  • DATE_TRUNC rounds down a date to the specified unit (e.g. month → 2026-01-01), while DATE_PART extracts just the numeric value of that unit (e.g. month → 1).

For more such questions