Display Dates not in database using sql server

Question

I'm trying to get the list of dates not between in two dates.

I've dates in the database from '2013-01-01' to '2013-01-31' except 25, 27, 28 dates are missing.

Now assume that I'm passing Start date = '2013-01-01' and End date = '2013-01-31' to get list of missing dates in the database i.e 25, 27, 28 dates.

Out put result should be as follows:

2013-01-25, 2013-01-27, 2013-01-28

Any help would be really appreciated.

Answer 1

If you are using SQL Server 2005+, you could try using something like

DECLARE @StartDate DATETIME = '01 Jan 2013',
@EndDate DATETIME = '31 Jan 2013'

;With Dates AS (
    SELECT @StartDate RunDate
    UNION ALL
    SELECT RunDate + 1
    FROM Dates
    WHERE RunDate + 1 <= @EndDate
)
SELECT * 
FROM Dates d LEFT JOIN
    YourTable yt ON d.RunDate = yt.YourDate
WHERE yt.YourDate IS NULL
OPTION (MaxRecursion 0)

In this query, we are using a recursive CTE to generate the date series (the OPTION (MaxRecursion 0) is to ensure that you do not receive a recursive error), then we are joining to the table in question, and only returning values that are missing from that table.