Get datetime between start and end datetime Start_Date                           End_Date
2019-09-10 17:48:00.000        2019-09-10 18:00:00.000
2019-09-10 17:00:00.000        2019-09-10 17:45:00.000
2019-09-10 17:30:00.000        2019-09-10 18:00:00.000
2019-09-11 08:00:00.000        2019-09-11 09:30:00.000
2019-09-11 08:30:00.000        2019-09-11 09:30:00.000
2019-09-11 12:00:00.000        2019-09-11 13:00:00.000
2019-09-11 12:30:00.000        2019-09-11 13:15:00.000
2019-09-11 12:15:00.000        2019-09-11 13:15:00.000


If i pass Start_Date and End_Date in Query
for e.g Start_Date =  '2019-09-11 12:00.000' and End_Date='2019-09-11 13:15.000'

Expected Result:  

Start_Date                        End_date
2019-09-11 12:00:00.000    2019-09-11 13:00:00.000
2019-09-11 12:30:00.000    2019-09-11 13:15:00.000
2019-09-11 12:15:00.000    2019-09-11 13:15:00.000
Full overlap:

SELECT * 
FROM source
WHERE Start_Date &gt;= '2019-09-11 12:00.000' 
  AND End_Date &lt;= '2019-09-11 13:15.000'


Partial overlap (not adjacent only, for adjacents replace strong unequiations &gt; and &gt; with safe ones &gt;= and &lt;=):

SELECT * 
FROM source
WHERE End_Date &gt; '2019-09-11 12:00.000' 
  AND Start_Date &lt; '2019-09-11 13:15.000'


fiddle