SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.DATA1
(
  ID integer PRIMARY KEY,
  STARTDATE datetime NOT NULL,
  ENDDATE datetime NOT NULL,
);

CREATE UNIQUE INDEX i1 ON dbo.DATA1 (STARTDATE DESC, ID ASC) INCLUDE (ENDDATE);
CREATE UNIQUE INDEX i2 ON dbo.DATA1 (ENDDATE ASC, ID DESC) INCLUDE (STARTDATE);

INSERT INTO DATA1 (ID, STARTDATE, ENDDATE) VALUES 
  (1, '2018-08-17 12:00:00', '2018-08-20 12:00:00'),
  (2, '2018-08-22 12:00:00', '2018-08-25 12:00:00'),
  (3, '2018-08-24 12:00:00', '2018-08-29 12:00:00'),
  (4, '2018-08-26 12:00:00', '2018-09-02 12:00:00'),
  (5, '2018-09-05 12:00:00', '2018-09-25 12:00:00'),
  (6, '2018-08-23 12:00:00', '2018-08-30 12:00:00'),
  (7, '2018-08-17 12:00:00', '2018-08-20 12:00:00'),
  (8, '2018-08-22 12:00:00', '2018-08-25 12:00:00'),
  (9, '2018-08-24 12:00:00', '2018-08-29 12:00:00'),
  (10, '2018-08-26 12:00:00', '2018-09-02 12:00:00'),
  (11, '2018-09-05 12:00:00', '2018-09-25 12:00:00'),
  (12, '2018-08-23 12:00:00', '2018-08-30 12:00:00');
12 rows affected

DECLARE @STARTDATE DATETIME = '2018-08-24 12:00:00';
DECLARE @ENDDATE DATETIME = '2018-08-31 12:00:00';

WITH MinStart AS
(
    -- Starting range
    SELECT TOP (1) 
        D.ID,
        D.STARTDATE
    FROM dbo.DATA1 AS D
    WHERE 
        D.STARTDATE >= @STARTDATE
        AND D.ENDDATE <= @ENDDATE
    ORDER BY 
        D.ID ASC

    UNION ALL

    SELECT
        P1.ID,
        P1.STARTDATE
    FROM 
    (
        -- Overlapping ranges with an earlier start date
        -- Numbered starting with the highest start date
        SELECT
            RN = ROW_NUMBER() OVER (
                ORDER BY D.STARTDATE DESC, D.ID ASC),
            D.ID,
            D.STARTDATE
        FROM MinStart AS R
        JOIN dbo.DATA1 AS D
            ON D.ENDDATE >= R.STARTDATE
            AND D.STARTDATE < R.STARTDATE
    ) AS P1
    WHERE
        -- Highest start date only
        P1.RN = 1
)
SELECT
    MS.ID,
    MS.STARTDATE
FROM MinStart AS MS
OPTION (MAXRECURSION 0);


ID
STARTDATE


3
24/08/2018 12:00:00

6
23/08/2018 12:00:00

2
22/08/2018 12:00:00


DECLARE @STARTDATE DATETIME = '2018-08-24 12:00:00';
DECLARE @ENDDATE DATETIME = '2018-08-31 12:00:00';

WITH MaxEnd AS
(
    -- Starting range
    SELECT TOP (1) 
        D.ID, 
        D.ENDDATE
    FROM dbo.DATA1 AS D
    WHERE
        D.STARTDATE >= @STARTDATE
        AND D.ENDDATE <= @ENDDATE
    ORDER BY 
        D.ID ASC

    UNION ALL

    SELECT
        P1.ID,
        P1.ENDDATE
    FROM 
    (
        -- Overlapping ranges with a later end date
        -- Numbered starting with the lowest end date
        SELECT
            RN = ROW_NUMBER() OVER (
                ORDER BY D.ENDDATE ASC, D.ID ASC),
            D.ID,
            D.ENDDATE
        FROM MaxEnd AS R
        JOIN dbo.DATA1 AS D
            ON D.ENDDATE > R.ENDDATE
            AND D.STARTDATE < R.ENDDATE
    ) AS P1
    WHERE 
        -- Lowest end date only
        P1.RN = 1
)
SELECT
    ME.ID,
    ME.ENDDATE
FROM MaxEnd AS ME
OPTION (MAXRECURSION 0);


ID
ENDDATE


3
29/08/2018 12:00:00

6
30/08/2018 12:00:00

4
02/09/2018 12:00:00
ID	STARTDATE
3	24/08/2018 12:00:00
6	23/08/2018 12:00:00
2	22/08/2018 12:00:00
ID	ENDDATE
3	29/08/2018 12:00:00
6	30/08/2018 12:00:00
4	02/09/2018 12:00:00