How to Generate a Row for Each Date Between Two Dates in SQL
Generating a row for each date between two dates is a common requirement in SQL, especially when you need to create date ranges for reports, fill in missing data points, or generate calendar tables. This technique is invaluable for data analysis, scheduling systems, and time-series reporting Not complicated — just consistent..
Not the most exciting part, but easily the most useful Easy to understand, harder to ignore..
Understanding the Problem
When working with date ranges in SQL, you often encounter scenarios where you need to display every single date between a start and end date, even if no data exists for some of those dates. This creates a "gap" in your data visualization or reporting, which can lead to misleading conclusions. As an example, if you're tracking daily sales and there are no sales on weekends, you might want to still show those weekend dates with zero sales rather than omitting them entirely That's the part that actually makes a difference..
Method 1: Using Recursive CTEs (Common Table Expressions)
The most versatile approach for generating date sequences is using recursive Common Table Expressions. This method works across most modern SQL databases including PostgreSQL, SQL Server, MySQL 8.0+, and Oracle.
WITH RECURSIVE date_series AS (
-- Base case: start with the initial date
SELECT DATE '2024-01-01' AS date_value
UNION ALL
-- Recursive case: increment by one day
SELECT date_value + INTERVAL '1 day'
FROM date_series
WHERE date_value < DATE '2024-01-10'
)
SELECT date_value
FROM date_series
ORDER BY date_value;
This query generates ten rows, one for each date from January 1st to January 10th, 2024. The recursive CTE works by starting with your initial date and repeatedly adding one day until it reaches your end date.
Method 2: Using Generate Series Function (PostgreSQL)
If you're using PostgreSQL, you have access to the powerful generate_series() function, which is specifically designed for creating sequences:
SELECT generate_series(
DATE '2024-01-01',
DATE '2024-01-10',
INTERVAL '1 day'
)::date AS date_value
ORDER BY date_value;
This one-liner accomplishes the same result as the recursive CTE but is more concise and potentially more efficient for large date ranges The details matter here..
Method 3: Using Numbers Table or Calendar Table
For databases without recursive CTE support or when you need better performance, you can use a numbers table approach. This method requires a pre-existing table with sequential numbers:
-- Assuming you have a numbers table called 'numbers' with column 'n'
SELECT DATE '2024-01-01' + INTERVAL '1 day' * n AS date_value
FROM numbers
WHERE DATE '2024-01-01' + INTERVAL '1 day' * n <= DATE '2024-01-10'
ORDER BY date_value;
Method 4: Cross Join with System Tables (SQL Server)
In SQL Server, you can make use of system tables to generate date sequences without a numbers table:
SELECT DATEADD(day, number, '2024-01-01') AS date_value
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS number
FROM sys.all_objects
) AS numbers
WHERE DATEADD(day, number, '2024-01-01') <= '2024-01-10'
ORDER BY date_value;
Practical Applications
Filling Missing Data in Reports
One of the most common uses for date generation is creating complete time series for reporting. When you join this date series with your actual data using a LEFT JOIN, you confirm that all dates appear in your results:
WITH date_series AS (
SELECT DATE '2024-01-01' AS report_date
UNION ALL
SELECT report_date + INTERVAL '1 day'
FROM date_series
WHERE report_date < DATE '2024-01-31'
)
SELECT
ds.report_date,
COALESCE(sales.total_amount, 0) AS daily_sales
FROM date_series ds
LEFT JOIN (
SELECT sale_date, SUM(amount) AS total_amount
FROM sales_transactions
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY sale_date
) sales ON ds.report_date = sales.sale_date
ORDER BY ds.report_date;
Creating Calendar Tables
Database administrators often use date generation to build comprehensive calendar tables that include holidays, workdays, and other date-related attributes:
WITH RECURSIVE calendar AS (
SELECT
DATE '2024-01-01' AS calendar_date,
EXTRACT(DOW FROM DATE '2024-01-01') AS day_of_week,
CASE
WHEN EXTRACT(DOW FROM DATE '2024-01-01') IN (0, 6) THEN 'Weekend'
ELSE 'Weekday'
END AS day_type
UNION ALL
SELECT
calendar_date + INTERVAL '1 day',
EXTRACT(DOW FROM calendar_date + INTERVAL '1 day'),
CASE
WHEN EXTRACT(DOW FROM calendar_date + INTERVAL '1 day') IN (0, 6) THEN 'Weekend'
ELSE 'Weekday'
END
FROM calendar
WHERE calendar_date < DATE '2024-12-31'
)
SELECT * FROM calendar;
Performance Considerations
When generating large date ranges, performance becomes critical. Here are some optimization strategies:
- Limit Date Range: Always use WHERE clauses to limit your date range to only what's necessary
- Use Set-Based Operations: Avoid row-by-row processing when possible
- Pre-Build Calendar Tables: For frequently used date ranges, consider creating permanent calendar tables
- Index Your Date Columns: Ensure proper indexing on date columns used in joins
Common Challenges and Solutions
Handling Different Date Types
Different SQL databases handle date arithmetic differently. Practically speaking, postgreSQL uses INTERVAL, while SQL Server uses DATEADD() and DATEDIFF(). MySQL uses DATE_ADD() or the + operator with INTERVAL.
Time Zone Considerations
When generating date sequences, consider whether your application needs to account for time zones. If you're working with timestamps rather than dates, you may need to adjust for daylight saving time changes.
Leap Years and Month Boundaries
Most SQL date functions automatically handle leap years and varying month lengths, but it's always good practice to test your date generation logic with edge cases like February 29th in leap years.
Advanced Techniques
Generating Business Days Only
You can modify the basic date generation to skip weekends:
WITH RECURSIVE business_days AS (
SELECT DATE '2024-01-01' AS date_value,
EXTRACT(DOW FROM DATE '2024-01-01') AS day_of_week
UNION ALL
SELECT
CASE
WHEN day_of_week = 6 THEN date_value + INTERVAL 2 days
WHEN day_of_week = 0 THEN date_value + INTERVAL 1 day
ELSE date_value + INTERVAL 1 day
END,
EXTRACT(DOW FROM CASE
WHEN day_of_week = 6 THEN date_value + INTERVAL 2 days
WHEN day_of_week = 0 THEN date_value + INTERVAL 1 day
ELSE date_value + INTERVAL 1 day
END)
FROM business_days
WHERE date_value < DATE '2024-01-31'
)
SELECT date_value FROM business_days WHERE day_of_week NOT IN (0, 6);
Including Holidays
For more sophisticated calendar generation, you can join with a holidays table to exclude specific dates:
WITH RECURSIVE date_series AS (
SELECT DATE '
```sql
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS calendar_date
UNION ALL
SELECT calendar_date + INTERVAL '1 day'
FROM date_series
WHERE calendar_date < DATE '2024-12-31'
)
SELECT
ds.calendar_date,
CASE
WHEN EXTRACT(DOW FROM ds.calendar_date) IN (0, 6) THEN 'Weekend'
ELSE 'Weekday'
END AS day_type,
h.holiday_name
FROM date_series ds
LEFT JOIN holidays h
ON ds.calendar_date = h.holiday_date
WHERE h.holiday_date IS NULL;
This query builds a continuous date series for the year, tags each day as a weekday or weekend, and then left‑joins a holidays table. Day to day, by filtering on WHERE h. holiday_date IS NULL, the result set automatically excludes any dates that appear in the holidays table, giving you a clean list of business days free from both weekends and observed holidays.
The official docs gloss over this. That's a mistake.
Putting It All Together
When you combine the basic calendar generation, business‑day filtering, and holiday exclusion, you obtain a versatile date dimension that can serve reporting, scheduling, and analytics layers. A well‑structured calendar table not only simplifies date‑related calculations but also improves query performance by centralizing date logic in a single, indexed structure.
Conclusion
Whether you need a simple list of dates, a filtered set of weekdays, or a comprehensive calendar that respects holidays, the recursive CTE approach provides a flexible foundation across PostgreSQL, SQL Server, and MySQL. By applying the performance best practices—limiting ranges, using set‑based operations, and pre‑building calendar tables—you check that your date handling remains both efficient and maintainable. With these
Beyond the basic recursive CTE, many production environments benefit from materializing the date dimension once and reusing it across multiple queries. A materialized calendar table eliminates the overhead of repeatedly generating rows and allows you to add richer attributes—such as fiscal periods, shift codes, or custom business‑rule flags—without complicating the core query logic Less friction, more output..
Creating a materialized calendar
-- PostgreSQL example
CREATE MATERIALIZED VIEW dim_calendar AS
WITH RECURSIVE date_series AS (
SELECT DATE '2000-01-01' AS calendar_date
UNION ALL
SELECT calendar_date + INTERVAL '1 day'
FROM date_series
WHERE calendar_date < DATE '2099-12-31'
)
SELECT
calendar_date,
EXTRACT(YEAR FROM calendar_date) AS year,
EXTRACT(MONTH FROM calendar_date) AS month,
EXTRACT(DAY FROM calendar_date) AS day,
TO_CHAR(calendar_date, 'Day') AS day_name,
EXTRACT(DOW FROM calendar_date) AS day_of_week, -- 0 = Sunday
CASE WHEN EXTRACT(DOW FROM calendar_date) IN (0,6) THEN FALSE ELSE TRUE END AS is_weekday,
-- placeholder for holiday flag; will be updated later
FALSE AS is_holiday
FROM date_series
WITH DATA;
-- Build indexes that speed up typical filters
CREATE UNIQUE INDEX idx_dim_calendar_date ON dim_calendar(calendar_date);
CREATE INDEX idx_dim_calendar_weekday ON dim_calendar(is_weekday);
CREATE INDEX idx_dim_calendar_ym ON dim_calendar(year, month);
After the view is built, you can refresh it nightly (or whenever your holiday table changes) and join it to fact tables:
REFRESH MATERIALIZED VIEW dim_calendar;
SELECT
f.That's why transaction_date = c. *,
c.calendar_date,
c.is_weekday,
c.In practice, is_holiday
FROM fact_sales f
JOIN dim_calendar c
ON f. calendar_date
WHERE c.is_weekday = TRUE
AND c.
**Handling fiscal calendars**
Organizations often run on a fiscal year that does not align with the calendar year. Adding fiscal attributes is straightforward:
```sql
ALTER TABLE dim_calendar ADD COLUMN fiscal_year INT;
ALTER TABLE dim_calendar ADD COLUMN fiscal_quarter INT;
ALTER TABLE dim_calendar ADD COLUMN fiscal_month INT;
UPDATE dim_calendar
SET
fiscal_year = CASE
WHEN EXTRACT(MONTH FROM calendar_date) >= 4 THEN EXTRACT(YEAR FROM calendar_date) + 1
ELSE EXTRACT(YEAR FROM calendar_date)
END,
fiscal_quarter = ((EXTRACT(MONTH FROM calendar_date) + 2) % 12) / 3 + 1,
fiscal_month = ((EXTRACT(MONTH FROM calendar_date) + 2) % 12) + 1;
If your fiscal calendar follows a 4‑4‑5 or 5‑4‑4 pattern, you can compute those values with a lookup table or a more elaborate CASE expression, but the principle remains the same: store the derived columns once and reuse them Simple as that..
Incorporating flexible work schedules
Some businesses operate on rotating shifts or have alternative workweeks (e.g., a four‑day workweek) That alone is useful..
CREATE TABLE work_pattern (
pattern_id SMALLINT PRIMARY KEY,
monday BOOLEAN NOT NULL,
tuesday BOOLEAN NOT NULL,
wednesday BOOLEAN NOT NULL,
thursday BOOLEAN NOT NULL,
friday BOOLEAN NOT NULL,
saturday BOOLEAN NOT NULL,
sunday BOOLEAN NOT NULL
);
-- Example: standard Mon‑Fri
INSERT INTO work_pattern VALUES (1, TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE);
-- Example: four‑day week (Mon‑Thu)
INSERT INTO work_pattern VALUES (2, TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE);
Join the calendar to the pattern to flag working days:
SELECT
c.calendar_date,
wp.pattern_id,
CASE
WHEN wp.monday AND EXTRACT(DOW FROM c.calendar_date) = 1 THEN TRUE
WHEN wp.tuesday AND EXTRACT(DOW FROM c.calendar_date) = 2 THEN TRUE
WHEN wp.wednesday AND EXTRACT(DOW FROM c.calendar_date) = 3 THEN TRUE
WHEN wp.thursday AND EX