select convert (varchar (15), DateTo, 107) as missingFrom,
  convert (varchar (15), NextDateFrom - 1, 107) as missingTo
from (
  select distinct DateFrom, DateTo + 1 as DateTo, 
     ( select MIN (dateFrom) 
        from dateranges 
        where DateTo > D.DateTo
     ) as NextDateFrom
  from dateranges D
) X
where DateTo < NextDateFrom

select DateTo as missingFrom,
  NextDateFrom as missingTo
from (
  select distinct DateFrom, DateTo as DateTo, 
     (select MIN (dateFrom) 
        from dateranges 
        where DateTo > D.DateTo
     ) as NextDateFrom
  from dateranges D
) X
where DateTo < NextDateFrom

SELECT 
 CONVERT(VARCHAR(20), DATEADD(DAY, 1, c.[to]), 107), 
 CONVERT(VARCHAR(20), DATEADD(DAY, -1, MIN(d.[from])), 107)
 
FROM
 (SELECT  [from], [to] = MAX([to])
        FROM
           (SELECT [from] = MIN(b.dateFrom), [to] = MAX(b.DateTo)
              FROM    dateranges a INNER JOIN dateranges b
                  ON a.dateFrom BETWEEN b.datefrom AND b.DateTo 
          OR a.dateTo BETWEEN b.datefrom AND b.DateTo
              GROUP BY a.datefrom
           ) f
        GROUP BY [from]--get the widest range from every start-date
      ) c 
   CROSS JOIN 
     (SELECT [from], [to] = MAX([to])
         FROM    
            (SELECT [from] = MIN(b.dateFrom), [to] = MAX(b.DateTo)
                FROM    dateranges a INNER JOIN dateranges b
                     ON a.dateFrom BETWEEN b.datefrom AND b.DateTo 
                         OR a.dateTo BETWEEN b.datefrom AND b.DateTo
                GROUP BY a.datefrom
              ) f
          GROUP BY [from] )d
WHERE d.[from] > c.[to]
GROUP BY c.[to]
HAVING DATEDIFF( DAY, c.[to], MIN(d.[from]))>1

SELECT
    DateTo + 1,
    (
        SELECT
            MIN (DateFrom) - 1
        FROM
            DateRanges D4
        WHERE
            D4.DateTo > D1.DateTo
        AND
            D4.DateFrom > D1.DateTo
    )
FROM
    DateRanges D1
WHERE
    NOT EXISTS (SELECT * FROM DateRanges D2 
   WHERE D1.DateTo + 1 BETWEEN D2.DateFrom AND D2.DateTo) 
     AND DateTo < (SELECT MAX (DateTo) FROM DateRanges)

;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
Q1 AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4),
Q2 AS (SELECT L.N FROM dateRanges dR
   JOIN Q1 L ON L.N 
          BETWEEN CAST(dR.dateFrom AS INT) 
          AND CAST(dR.dateTo AS INT)),
Q3 AS (SELECT y.N FROM Q1 y
   LEFT JOIN Q2 z ON y.N = z.N
   WHERE z.N IS NULL
   AND y.N BETWEEN 
                 (SELECT MIN(CAST(dateFrom AS INT)) 
                    FROM dateRanges)
          AND 
                  (SELECT MAX(CAST(dateTo AS INT)) 
                    FROM dateRanges)),
Q4 AS (SELECT f.N Whither, e.N Thither, e.N-f.N diff
   FROM (
       SELECT a.N
       FROM Q3 a
       LEFT JOIN Q3 b ON a.N = b.N-1
       WHERE b.N IS NULL
   ) e
   JOIN (
       SELECT c.N
       FROM Q3 c
       LEFT JOIN Q3 d ON c.N = d.N+1
       WHERE d.N IS NULL
   ) f ON 1=1
   WHERE e.N-f.N >=0),
Q5 AS (SELECT Whither, 
             Thither, 
             diff, 
             ROW_NUMBER() OVER(PARTITION 
                BY Whither ORDER BY diff) RN FROM Q4
       )
SELECT CONVERT(VARCHAR(12), CONVERT(DATETIME, Whither), 107) 
            AS missingFrom, 
       CONVERT(VARCHAR(12), CONVERT(DATETIME, Whither), 107) 
            AS missingTo
FROM Q5
WHERE RN = 1
ORDER BY Whither

Ok, so I'm a 2005 guy, but someone could do a translation for 2000... I have an uneasy feeling that this might break somehow, but so far I can't figure out how... can anyone else?

SELECT 
   CONVERT(VARCHAR(20), mf.missDateFrom, 107), 
   CONVERT(VARCHAR(20), mt.missDateTo, 107) 
FROM 
(
  SELECT 
  row_number() OVER (ORDER BY dateFrom) - 1 AS idx, 
  DATEADD(DAY, -1, dateFrom) AS missDateTo 
  FROM dateRanges
) AS mt
INNER JOIN 
(
  SELECT 
  row_number() OVER (ORDER BY dateTo) AS idx,
  DATEADD(DAY, 1, dateTo) AS missDateFrom
  FROM dateRanges
) AS mf ON mf.idx = mt.idx
WHERE mf.missDateFrom <= mt.missDateTo

Uhm, stricly 2005 here.

Hope the pasting works. Nice puzzle!

SELECT DISTINCT * FROM (
   SELECT  
      CONVERT(VARCHAR(20), DATEADD(d, 1, t1.dateto), 107) AS MissingFrom,
      CONVERT(VARCHAR(20), DATEADD(d, -1, t2.datefrom), 107) AS MissingTO
   FROM    DateRanges t1
   INNER JOIN DateRanges t2
   ON      DATEDIFF(d, t1.DateTo, t2.DateFrom) > 1
   AND     NOT EXISTS (SELECT * FROM DateRanges t3 
                       WHERE (t3.DateFrom < t2.DateFrom 
                       AND t3.DateTo > t1.DateTo))
                       ) AS t

Here's the problem, puzsol

--Truncate table dateRanges and insert the next three rows:

INSERT INTO dateRanges VALUES('Apr 01, 2000' ,'Apr 06, 2000')
INSERT INTO dateRanges VALUES('Apr 02, 2000' ,'Apr 04, 2000')
INSERT INTO dateRanges VALUES('Apr 08, 2000' ,'Apr 13, 2000')

--, your query will retrieve the correct result

Apr 07, 2000	Apr 07, 2000

--Truncate table dateRanges again and insert the next three rows:

INSERT INTO dateRanges 
VALUES('Apr 01 2000 00:00:01','Apr 06 2000 19:36:00')
INSERT INTO dateRanges 
VALUES('Apr 02 2000 00:00:02','Apr 04 2000 19:30:00')
INSERT INTO dateRanges 
VALUES('Apr 08 2000 00:00:04','Apr 13 2000 19:00:00')

--This time the query doesn’t retrieve any result, even though 
--Apr 07 is still missing.

Ok, So I didn't cater for times (which the problem is a bit vague on, unless I missed something)... but there's still nothing wrong with the method as such, just the location of the math....

This version works for the dates entered:

SELECT CONVERT(VARCHAR(20), mf.missDateFrom, 107) AS dateFrom
, CONVERT(VARCHAR(20), DATEADD(DAY, -1, mt.missDateTo), 107) dateTo
FROM 
(
  SELECT row_number() OVER (ORDER BY dateFrom) -1 AS idx
  , dateFrom AS missDateTo 
  FROM dateRanges
) AS mt
INNER JOIN 
(
  SELECT row_number() OVER (ORDER BY dateTo) AS idx
  , DATEADD(DAY, 1, dateTo) AS missDateFrom 
  FROM dateRanges
) AS mf ON mf.idx = mt.idx
WHERE mf.missDateFrom < mt.missDateTo

The other way you could do it is to strip the time - but that would probably take a bit longer as it would have to be done before the comparison.... I'm still searching for a pattern of dates that would break it...

Has anyone performed any time comparisons on a large database yet? I'm wondering if the sub-query approach is faster than the merging of two datasets.

INSERT INTO dateRanges VALUES('May 22, 2000','May 25, 2000')

SELECT DISTINCT 
      (SELECT DATEADD(MS,3,MAX(dateTo)) 
          FROM dateRanges c 
          WHERE c.dateTo < a.dateFrom) AS missingFrom,
       DATEADD(MS,-3,a.dateFrom
       ) AS missingTo
FROM   dateRanges a
WHERE  NOT EXISTS  
       (SELECT * FROM dateRanges b 
            WHERE DATEADD(MS,-3,a.dateFrom) BETWEEN b.dateFrom AND b.dateTo)
             AND a.dateFrom >(SELECT MIN(dateFrom) FROM dateRanges
       )

SELECT DATEADD( DAY, 1, a.dateTo ) missingDateFrom,
       DATEADD( DAY, -1, MIN( c.dateFrom ) ) missingDateTo
FROM   DateRanges a
       LEFT JOIN DateRanges b 
         ON  DATEADD( DAY, 1, a.dateTo ) BETWEEN b.dateFrom AND b.dateTo
       LEFT JOIN DateRanges c
         ON DATEADD( DAY, 1, a.dateTo ) < c.dateFrom
WHERE  b.dateTo IS NULL AND c.DateFrom IS NOT NULL
GROUP BY DATEADD( DAY, 1, a.dateTo )

If the source data used something more granular than days, just change the DateAdd parameters slightly, e.g.

SELECT DATEADD( ms, 3, a.dateTo ) missingDateFrom,
       DATEADD( ms, -3, MIN( c.dateFrom ) ) missingDateTo
FROM   DateRanges a
       LEFT JOIN DateRanges b 
         ON  DATEADD( ms, 3, a.dateTo ) BETWEEN b.dateFrom AND b.dateTo
       LEFT JOIN DateRanges c
         ON DATEADD( ms, 3, a.dateTo ) < c.dateFrom
WHERE  b.dateTo IS NULL AND c.DateFrom IS NOT NULL
GROUP BY DATEADD( ms, 3, a.dateTo )