SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE testdata
    (rn int, start_date datetime, end_date datetime)
;

INSERT INTO testdata
    (rn, start_date, end_date)
VALUES
    (1, '2000-01-01 00:00:00', '2000-01-31 00:00:00'),
    (2, '2000-01-05 00:00:00', '2000-01-25 00:00:00'),
    (3, '2000-01-10 00:00:00', '2000-01-20 00:00:00'),
    (4, '2000-01-01 00:00:00', '2000-01-10 00:00:00'),
    (5, '2000-01-20 00:00:00', '2000-01-31 00:00:00'),
    (6, '2000-01-01 00:00:00', '2000-01-31 00:00:00')
;
6 rows affected

SELECT *
FROM testdata todelete
LEFT JOIN testdata tokeep ON
    todelete.start_date >= tokeep.start_date AND
    todelete.end_date <= tokeep.end_date AND
    todelete.rn <> tokeep.rn AND (
        todelete.start_date > tokeep.start_date OR
        todelete.end_date < tokeep.end_date OR
        todelete.rn > tokeep.rn
    )
ORDER BY todelete.rn, tokeep.rn


rn
start_date
end_date
rn
start_date
end_date


1
01/01/2000 00:00:00
31/01/2000 00:00:00
null
null
null

2
05/01/2000 00:00:00
25/01/2000 00:00:00
1
01/01/2000 00:00:00
31/01/2000 00:00:00

2
05/01/2000 00:00:00
25/01/2000 00:00:00
6
01/01/2000 00:00:00
31/01/2000 00:00:00

3
10/01/2000 00:00:00
20/01/2000 00:00:00
1
01/01/2000 00:00:00
31/01/2000 00:00:00

3
10/01/2000 00:00:00
20/01/2000 00:00:00
2
05/01/2000 00:00:00
25/01/2000 00:00:00

3
10/01/2000 00:00:00
20/01/2000 00:00:00
6
01/01/2000 00:00:00
31/01/2000 00:00:00

4
01/01/2000 00:00:00
10/01/2000 00:00:00
1
01/01/2000 00:00:00
31/01/2000 00:00:00

4
01/01/2000 00:00:00
10/01/2000 00:00:00
6
01/01/2000 00:00:00
31/01/2000 00:00:00

5
20/01/2000 00:00:00
31/01/2000 00:00:00
1
01/01/2000 00:00:00
31/01/2000 00:00:00

5
20/01/2000 00:00:00
31/01/2000 00:00:00
6
01/01/2000 00:00:00
31/01/2000 00:00:00

6
01/01/2000 00:00:00
31/01/2000 00:00:00
1
01/01/2000 00:00:00
31/01/2000 00:00:00


SELECT *
FROM testdata todelete
WHERE EXISTS (
    SELECT 1
    FROM testdata tokeep
    WHERE
    todelete.start_date >= tokeep.start_date AND
    todelete.end_date <= tokeep.end_date AND
    todelete.rn <> tokeep.rn AND (
        todelete.start_date > tokeep.start_date OR
        todelete.end_date < tokeep.end_date OR
        todelete.rn > tokeep.rn
    )
)


rn
start_date
end_date


2
05/01/2000 00:00:00
25/01/2000 00:00:00

3
10/01/2000 00:00:00
20/01/2000 00:00:00

4
01/01/2000 00:00:00
10/01/2000 00:00:00

5
20/01/2000 00:00:00
31/01/2000 00:00:00

6
01/01/2000 00:00:00
31/01/2000 00:00:00

rn	start_date	end_date	rn	start_date	end_date
1	01/01/2000 00:00:00	31/01/2000 00:00:00	null	null	null
2	05/01/2000 00:00:00	25/01/2000 00:00:00	1	01/01/2000 00:00:00	31/01/2000 00:00:00
2	05/01/2000 00:00:00	25/01/2000 00:00:00	6	01/01/2000 00:00:00	31/01/2000 00:00:00
3	10/01/2000 00:00:00	20/01/2000 00:00:00	1	01/01/2000 00:00:00	31/01/2000 00:00:00
3	10/01/2000 00:00:00	20/01/2000 00:00:00	2	05/01/2000 00:00:00	25/01/2000 00:00:00
3	10/01/2000 00:00:00	20/01/2000 00:00:00	6	01/01/2000 00:00:00	31/01/2000 00:00:00
4	01/01/2000 00:00:00	10/01/2000 00:00:00	1	01/01/2000 00:00:00	31/01/2000 00:00:00
4	01/01/2000 00:00:00	10/01/2000 00:00:00	6	01/01/2000 00:00:00	31/01/2000 00:00:00
5	20/01/2000 00:00:00	31/01/2000 00:00:00	1	01/01/2000 00:00:00	31/01/2000 00:00:00
5	20/01/2000 00:00:00	31/01/2000 00:00:00	6	01/01/2000 00:00:00	31/01/2000 00:00:00
6	01/01/2000 00:00:00	31/01/2000 00:00:00	1	01/01/2000 00:00:00	31/01/2000 00:00:00