Not sure what you want to achive, but have a look at example:
DECLARE @tmp TABLE (MyDate DATETIME)
INSERT INTO @tmp (MyDate)
VALUES ('2014-01-29'),
('2014-01-30'), ('2014-02-03'),
('2014-02-04'), ('2014-02-05'),
('2014-02-06'), ('2014-02-11'),
('2014-02-12'), ('2014-02-13'),
('2014-02-17'), ('2014-02-18'),
('2014-02-19'), ('2014-02-20'),
('2014-02-25'), ('2014-02-26'),
('2014-02-27'), ('2014-03-03'),
('2014-03-04'), ('2014-03-05'),
('2014-03-06'), ('2014-03-11'),
('2014-03-12'), ('2014-03-13')
SELECT t1.MyDate AS FirstDate, t2.MyDate AS SecondDate, DATEDIFF(d, t1.MyDate, t2.MyDate) AS DayDiff
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate) AS RowNo, MyDate
FROM @tmp
)
AS t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY MyDate) AS RowNo, MyDate
FROM @tmp
) AS t2
ON t1.RowNo = t2.RowNo-1
Result:
FirstDate SecondDate DayDiff
2014-01-29 00:00:00.000 2014-01-30 00:00:00.000 1
2014-01-30 00:00:00.000 2014-02-03 00:00:00.000 4
2014-02-03 00:00:00.000 2014-02-04 00:00:00.000 1
2014-02-04 00:00:00.000 2014-02-05 00:00:00.000 1
2014-02-05 00:00:00.000 2014-02-06 00:00:00.000 1
2014-02-06 00:00:00.000 2014-02-11 00:00:00.000 5
2014-02-11 00:00:00.000 2014-02-12 00:00:00.000 1
2014-02-12 00:00:00.000 2014-02-13 00:00:00.000 1
2014-02-13 00:00:00.000 2014-02-17 00:00:00.000 4
2014-02-17 00:00:00.000 2014-02-18 00:00:00.000 1
2014-02-18 00:00:00.000 2014-02-19 00:00:00.000 1
2014-02-19 00:00:00.000 2014-02-20 00:00:00.000 1
2014-02-20 00:00:00.000 2014-02-25 00:00:00.000 5
2014-02-25 00:00:00.000 2014-02-26 00:00:00.000 1
2014-02-26 00:00:00.000 2014-02-27 00:00:00.000 1
2014-02-27 00:00:00.000 2014-03-03 00:00:00.000 4
2014-03-03 00:00:00.000 2014-03-04 00:00:00.000 1
2014-03-04 00:00:00.000 2014-03-05 00:00:00.000 1
2014-03-05 00:00:00.000 2014-03-06 00:00:00.000 1
2014-03-06 00:00:00.000 2014-03-11 00:00:00.000 5
2014-03-11 00:00:00.000 2014-03-12 00:00:00.000 1
2014-03-12 00:00:00.000 2014-03-13 00:00:00.000 1
As you can see, you can compare dates by using
Join
. For further information, please see:
Visual Representation of SQL Joins[
^].
Using
WHERE condition[
^], you're able to filter your data to show dates where the distance between them is larger than 1 day:
WHERE DATEDIFF(d, t1.MyDate, t2.MyDate)>1
Try!