How to Find Missing Dates in SQL (Calendar Table Method)

The problem: GROUP BY can't show what isn't there

SELECT order_date, COUNT(*) AS order_count
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-10'
GROUP BY order_date;

-- If Jan 4th had zero orders, it simply never appears as a row —
-- there's nothing wrong with the query, there's nothing to group

A line chart built directly from this result will connect January 3rd straight to January 5th, making a zero-order day invisible instead of showing a dip to zero — which can hide a real outage or, less dramatically, just produce a misleading chart.

Building a date spine in PostgreSQL

SELECT GENERATE_SERIES('2026-01-01'::date, '2026-01-10'::date, '1 day'::interval)::date AS calendar_date;

-- LEFT JOIN real data onto it — every date appears, with 0 where there's no match
SELECT c.calendar_date, COUNT(o.order_id) AS order_count
FROM GENERATE_SERIES('2026-01-01'::date, '2026-01-10'::date, '1 day'::interval)::date AS c(calendar_date)
LEFT JOIN orders o ON o.order_date = c.calendar_date
GROUP BY c.calendar_date
ORDER BY c.calendar_date;

Building a date spine in SQL Server

WITH calendar AS (
  SELECT CAST('2026-01-01' AS DATE) AS calendar_date
  UNION ALL
  SELECT DATEADD(DAY, 1, calendar_date)
  FROM calendar
  WHERE calendar_date < '2026-01-10'
)
SELECT c.calendar_date, COUNT(o.order_id) AS order_count
FROM calendar c
LEFT JOIN orders o ON o.order_date = c.calendar_date
GROUP BY c.calendar_date
OPTION (MAXRECURSION 366);

The MAXRECURSION hint matters here — SQL Server's default recursion limit is 100, which silently truncates a date range longer than 100 days unless it's explicitly raised.

Finding the actual missing dates

To list only the gaps, rather than a full report, filter for where the join found nothing:

SELECT c.calendar_date AS missing_date
FROM GENERATE_SERIES('2026-01-01'::date, '2026-01-10'::date, '1 day'::interval)::date AS c(calendar_date)
LEFT JOIN orders o ON o.order_date = c.calendar_date
WHERE o.order_id IS NULL;

This is the same core pattern used to find gaps in an invoice number sequence or any other supposedly-continuous series — a calendar table is just a date-typed version of the same idea covered in Identify Gaps and Islands in SQL.

When to use a permanent calendar table

For reports run frequently or across a long, fixed range, a small permanent table — one row per day, spanning a few decades — is often better than regenerating a series on every query. It also has room for extra columns a generated series doesn't have on its own:

CREATE TABLE calendar_dates (
  calendar_date  DATE PRIMARY KEY,
  day_of_week    VARCHAR(10),
  is_weekend     BOOLEAN,
  fiscal_quarter INT
);
-- Populated once (often via GENERATE_SERIES or a recursive CTE), then reused by every report

Common mistakes

• Trusting a GROUP BY result to represent every date in a range — it only ever shows dates that actually have rows.
• Forgetting SQL Server's default 100-level recursion limit when building a date spine longer than about three months with a recursive CTE.
• Comparing a DATETIME column to a DATE-typed calendar without truncating the time portion first, causing every join to silently fail to match.
• Regenerating a multi-year date series on every single report run instead of using a small permanent calendar table for frequently-run reports.