🗓️ Two Approaches to Generating Calendar Dates in SQL Server

When working with time-based reporting or scheduling systems, it’s often necessary to generate a list of calendar dates. Whether you’re counting workdays, building timelines, or joining with other datasets, having a calendar table—or generating dates on the fly—is crucial. Let’s explore two popular methods of generating calendar dates in SQL Server.

---

✅ 1. Using a Recursive CTE (Common Table Expression)

A recursive CTE can dynamically generate a list of dates without relying on any external table:

WITH Calendar AS (
    SELECT CAST('2024-07-01' AS DATE) AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM Calendar
    WHERE DateValue < '2025-06-30'
)
SELECT * FROM Calendar
OPTION (MAXRECURSION 0);

🔍 Pros:

• No dependency on system tables.
• Simple and self-contained.

⚠️ Cons:

• Slightly less performant for very large ranges.
• MAXRECURSION default is 100; you must override it for larger date ranges.

---

✅ 2. Using master.dbo.spt_values (or a numbers/tally table)

This system table contains thousands of rows and can be repurposed to generate sequential numbers (and thus, dates):

SELECT DATEADD(DAY, number, '2024-07-01') AS DateValue
FROM master.dbo.spt_values
WHERE type = 'P'
  AND number BETWEEN 0 AND DATEDIFF(DAY, '2024-07-01', '2025-06-30');

🔍 Pros:

• Faster performance for large sets.
• No recursion required.

⚠️ Cons:

• Relies on an undocumented system table (spt_values), which may be unsupported or unavailable in some SQL Server environments (especially Azure SQL DB).
• Less self-explanatory for beginners.

---

🧠 When to Use Which?

Use Case	Recommended Method
Small date ranges (e.g. 1 year)	Recursive CTE
Large date ranges (many years)	Tally table or spt_values
Need portability and readability	Recursive CTE
Need performance	Tally/spt_values

---

🏁 Conclusion

Both approaches have their place. The recursive CTE is elegant and portable, great for small to medium needs. If you’re working with large datasets or care about performance, using spt_values (or better, your own numbers table) is a strong alternative.

-- Suggested: Create your own Numbers table for safety
CREATE TABLE dbo.Numbers (n INT PRIMARY KEY);
WITH E1(n) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(n) AS (
    SELECT 1 FROM E1 a CROSS JOIN E1 b  -- 16 rows
),
E4(n) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM E2 a CROSS JOIN E2 b
)
INSERT INTO dbo.Numbers(n)
SELECT n FROM E4 WHERE n < 10000;

👉 Tip: For production systems, consider creating a permanent Calendar table with pre-generated dates and useful attributes (week number, holiday flag, etc.) to simplify your queries.