How to Find Missing Dates in a Sequence Using SQL

Hey, I’m Nishant Chandravanshi. I’ve spent over a decade working hands-on with data — from writing SQL queries and building Power BI dashboards to orchestrating ETL pipelines. If you’re someone who solves real-world data engineering or reporting problems, I’ve probably been in your shoes.

Today, I want to share a simple but powerful SQL trick that I’ve personally used in production — whether it was troubleshooting missing sales records, identifying log gaps, or validating time-series data.

How to find missing dates from a date column using Recursive CTE in SQL Server.

This is not a theoretical concept — I’ve used this exact trick in production when debugging sales reports, checking log gaps, or validating timeseries data.

Let’s break it down — line by line — with real-world analogies and logical explanations.

Problem: Detect Missing Dates in a Table

Let’s say you have a sales table that records transactions for specific dates. But you suspect some dates are missing.

Here’s your starting dataset:

-- Drop if already exists
DROP TABLE IF EXISTS #SalesDates;

-- Create and insert data
CREATE TABLE #SalesDates (
  SaleDate DATE
);

INSERT INTO #SalesDates (SaleDate)
VALUES
  ('2024-01-01'),
  ('2024-01-02'),
  ('2024-01-04'),
  ('2024-01-06');

-- Missing Dates Output

So we’re missing:

2024-01-03  
2024-01-05

Goal:

Return only those missing dates between the minimum and maximum dates in the table.

Method 1: Step-by-Step Breakdown of the Code

Step 1: Define the Date Range

WITH DateRange AS (
  SELECT MIN(SaleDate) AS StartDate, MAX(SaleDate) AS EndDate
  FROM #SalesDates
)

-- Output of DateRange CTE

StartDate = 2024-01-01  
EndDate = 2024-01-06

We now know the date window we want to scan through.

Step 2: Generate All Dates in Between (Recursive CTE)

, AllDates AS (
  SELECT StartDate AS MissingDate
  FROM DateRange

  UNION ALL

  SELECT DATEADD(DAY, 1, MissingDate)
  FROM AllDates
  CROSS JOIN DateRange
  WHERE MissingDate < EndDate
)

What’s Happening Here?

• The anchor part: SELECT StartDate begins the sequence at the first date.

• The recursive part: DATEADD(DAY, 1, MissingDate) adds one day every time.

• This continues until it hits the EndDate.

So internally, the recursion works like this:

-- Output: Recursive Iteration of Dates

Iteration   MissingDate
1           2024-01-01
2           2024-01-02
3           2024-01-03
4           2024-01-04
5           2024-01-05
6           2024-01-06

Why This Line: CROSS JOIN DateRange?

You might ask:

“Why are we doing a CROSS JOIN here? There’s no ON clause!”

Great question.

Answer:

We do this only to make the EndDate available inside the recursive part.
Without this CROSS JOIN, SQL Server will throw an error like:

❌ “Invalid column ‘EndDate'”

Recursive CTEs cannot refer to outside CTEs unless you explicitly bring them in via a join — even if it’s a one-row constant table like DateRange.

So:

CROSS JOIN DateRange  
WHERE MissingDate < EndDate

This is just a trick to carry EndDate into the loop so we can say:
➡️ “Stop once MissingDate reaches EndDate”

Step 3: Filter Out Existing Dates

SELECT MissingDate  
FROM AllDates  
WHERE MissingDate NOT IN (SELECT SaleDate FROM #SalesDates)  
ORDER BY MissingDate  
OPTION (MAXRECURSION 1000);

-- Output: Final Missing Dates

MissingDate  
------------
2024-01-03  
2024-01-05

Recap of Logic Flow

Part of Code	Role
DateRange	Gets StartDate and EndDate
AllDates (recursive CTE)	Generates all dates in that range
CROSS JOIN DateRange	Makes EndDate accessible inside recursion
WHERE MissingDate < EndDate	Controls loop stopping condition
Final SELECT	Filters out the existing dates to show only missing ones

Real-Life Analogy

Think of this like taking school attendance:

• You have a list of all 6 school days.

• Your system shows attendance only for 4.

• You loop through all 6 days and check: “Did the student come?”

• The 2 days with no attendance → Missing dates

Bonus: Why Not Use a Calendar Table Instead?

Yes, you could use a calendar table or numbers table if your system has one.
But recursive CTEs are powerful for ad-hoc or one-time queries, especially when:

• You don’t want extra tables

• You want dynamic start/end dates

• You’re debugging in SQL Server Management Studio

Method 2: Step-by-Step Breakdown of the Code:

-- Generate sequence of dates between MIN and MAX SaleDate using a tally table
WITH DateRange AS (
    SELECT MIN(SaleDate) AS StartDate, MAX(SaleDate) AS EndDate FROM #SalesDates
),
Tally AS (
    SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM master.dbo.spt_values -- or use any large enough system table
),
AllDates AS (
    SELECT DATEADD(DAY, t.n, dr.StartDate) AS MissingDate
    FROM Tally t
    CROSS JOIN DateRange dr
    WHERE DATEADD(DAY, t.n, dr.StartDate) <= dr.EndDate
)
SELECT MissingDate
FROM AllDates
WHERE MissingDate NOT IN (SELECT SaleDate FROM #SalesDates)
ORDER BY MissingDate;

Step 1: Create a CTE to get the min and max dates from the original data

WITH DateRange AS (
    SELECT MIN(SaleDate) AS StartDate, MAX(SaleDate) AS EndDate
    FROM #SalesDates
)

Purpose:
Extracts the starting and ending date from your data so we can generate a complete date sequence between them.

MissingDate  
------------  
2024-01-03  
2024-01-05

 

Tally AS (
    SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM master.dbo.spt_values
),

Purpose:
This creates a list of numbers from 0 to 999 using ROW_NUMBER().
This is called a tally table or numbers table, commonly used to generate sequences (like dates, IDs, etc.) in SQL.

n  
--  
0  
1  
2  
...  
999

 

AllDates AS (
    SELECT DATEADD(DAY, t.n, dr.StartDate) AS MissingDate
    FROM Tally t
    CROSS JOIN DateRange dr
    WHERE DATEADD(DAY, t.n, dr.StartDate) <= dr.EndDate
)

Purpose:

• For each number n in the tally table, we add n days to the StartDate.

• This gives us every single date from StartDate to EndDate.

• CROSS JOIN is used so we can access dr.StartDate and dr.EndDate inside this expression.

Example:
If StartDate = '2024-01-01' and EndDate = '2024-01-06', then:

n	DATEADD(DAY, n, StartDate)
0	2024-01-01
1	2024-01-02
2	2024-01-03
3	2024-01-04
4	2024-01-05
5	2024-01-06
6	2024-01-07 ❌ (filtered by WHERE clause)

 

SELECT MissingDate
FROM AllDates
WHERE MissingDate NOT IN (SELECT SaleDate FROM #SalesDates)
ORDER BY MissingDate;

Purpose:

• From all generated dates, only select those that are not already in #SalesDates.

• This gives you the missing dates in the sequence.

MissingDate  
------------  
2024-01-03  
2024-01-05

In Summary:

Step	CTE Name	Purpose
1	DateRange	Get min and max dates from existing table
2	Tally	Generate numbers from 0 to 999
3	AllDates	Add numbers to StartDate to generate all dates
4	Final SELECT	Filter out dates already present in #SalesDates

Final Thoughts

As a data engineer working across industries, I’ve seen small things like missing dates cause major reporting issues — from sales mismatches to incorrect dashboards.

Knowing how to build a simple recursive CTE gives you an on-demand solution without any external dependency.

I hope this gave you clarity — not just about how it works, but why it works.

If you have any questions related to this topic, feel free to comment below!