How To find overlapping Dates in SQL Server

Question

I have a table which has startdatetime and enddatetime. how to find the the particuler datetime which is overlapping in other dates: see below example

create table #period (
    id int,
    starttime datetime,
    endtime datetime
  );

insert into #period values 
(1,'2013-10-10 08:00:00' , '2013-10-10 10:00:00'), 
(2,'2013-10-10 08:10:00' , '2013-10-10 08:20:00'), 
(3,'2013-10-10 08:10:00' , '2013-10-10 08:30:00') 
(4,'2013-10-10 08:15:00' , '2013-10-10 08:25:00') 

select * from #period


required output is '2013-10-10 08:15:00' , '2013-10-10 08:20:00' is getting overlapped in all the dates.

expected output: '2013-10-10 08:15:00' '2013-10-10 08:20:00' 5 Min

EDIT:

I apologies for previous question Here it is the proper details with example

create table #period 
(
    Sitecode varchar(20),
    svrname varchar(10),
    StartTime datetime,
    downtimeEnd datetime
) 
go  

insert into #period values 
('A','S1','2013-10-10 10:00:00' , '2013-10-10 11:00:00'), 
('A','S2','2013-10-10 10:00:00' , '2013-10-10 11:00:00'),
('A','S3','2013-10-10 10:00:00' , '2013-10-10 11:00:00'), 
('A','S1','2013-10-10 03:00:00' , '2013-10-10 03:30:00'), 
('A','S2','2013-10-10 06:30:00' , '2013-10-10 07:30:00')

select * from #period

Expected Output is:

Sitecode 'A' 
       Total 2.5 hours (below is the details)
       '2013-10-10 10:00:00' , '2013-10-10 11:00:00' getting overlapp - 1 hour
       '2013-10-10 03:00:00' , '2013-10-10 03:30:00' - .5 hour
       '2013-10-10 06:30:00' , '2013-10-10 07:30:00'- 1 hour

The output is group by sitecode. and the overlapping date considered only once. The final output is required:

Sitecode 'A' = 2.5 hours

Answer 1

This is one of few examples where it could make sense to use HAVING without GROUP BY (yes it's possible):

SELECT MAX(starttime), MIN(endtime)
FROM #period
HAVING MIN(endtime) > MAX(starttime);

SQLFiddle DEMO

Answer 2

If you have a set of periods and want the overlaps of all of them, then the overlaps are going to be between the maximum starttime and the minimum end time.

select cast(starttime as date) as thedate, max(starttime), min(endtime)
from #periods
group by cast(starttime as date);

This assumes that the start and end are on the same date. The first just extracts the date from the datetime values (and this may vary depending on the database). The version here is for SQL Server.

You have no overlaps when min(endtime) is less than max(starttime). So, to get dates with an overlap:

select cast(starttime as date) as thedate, max(starttime), min(endtime)
from #periods
group by cast(starttime as date)
having min(endtime) > max(starttime);

EDIT:

If you want overlaps within a site code, just add that into the `group by:

select sitecode, cast(starttime as date) as thedate, max(starttime), min(endtime)
from #periods
group by sitecode, cast(starttime as date)
having min(endtime) > max(starttime);