Finding gaps (missing records) in database records using SQL

Question

I have a table with records for every consecutive hour. Each hour has some value. I want a T-SQL query to retrieve the missing records (missing hours, the gaps). So for the DDL below, I should get a record for missing hour 04/01/2010 02:00 AM (assuming date range is between the first and last record). Using SQL Server 2005. Prefer a set based query.

DDL:
CREATE TABLE [Readings](
    [StartDate] [datetime] NOT NULL,
    [SomeValue] [int] NOT NULL
)
INSERT INTO [Readings]([StartDate], [SomeValue])
SELECT '20100401 00:00:00.000', 2 UNION ALL
SELECT '20100401 01:00:00.000', 3 UNION ALL
SELECT '20100401 03:00:00.000', 45

Answer 1

Assuming that all records are exact hours:

WITH    q(s, e) AS
        (
        SELECT  MIN(StartDate), MAX(StartDate)
        FROM    Readings
        UNION ALL
        SELECT  DATEADD(hour, 1, s), e
        FROM    q
        WHERE   s < e
        )
SELECT  *
FROM    q
WHERE   s NOT IN
        (
        SELECT  StartDate
        FROM    Readings
        )
OPTION (MAXRECURSION 0)