>>Thanks, but my primary keys are actually unique identifiers​.
>>
>Good to know :)
>
>Just thought I'd point out to you that your approach will return that record 5 overlaps record 6 (because the starting date of 6 is between the starting date end ending date of 5), and that record 6 overlaps record 5 (because the ending date of 5 is between the starting date end ending date of 6). Now if that is not a problem then your approach is ok . Otherwise, as is often the case when you join a table with itself you should use a smaller than test rather than not equal between the (unique) primary keys.
>
>Now (in addition) if you do want to test couples only once, you will have a problem with your records 1 and 2. If those would have been interchanged, you would not have spotted that there is an overlap there.
>

Thanks, once I get them all, even if I get some more than once, I am happy.

>>>You might want to replace

o1.pk <> o2.pk

by

o1.pk<o2.pk

, if you are returning the same overlap twice. And if you do you also need to check if a "lap" falls completely within the other "lap".
>>>
>>>>Hi,
>>>>
>>>>I have been tasked with finding out invalid data in a database. This is a simplified scenario:
>>>>
>>>>

create table #Overlaps (
>>>>PK int identity
>>>>,employeePK int
>>>>,StartDate Date
>>>>,Enddate Date)
>>>>
>>>>insert into #Overlaps
>>>>(employeePK, StartDate, EndDate)
>>>>VALUES (1, '2013-01-01', '2016-08-31') -- this overlaps with the next row
>>>>,(1, '2014-08-01', '2014-08-31') -- this overlaps with previous as both start date '2014-08-01' is greater than '2013-01-01' and less than '2016-08-31' and end date '2014-08-31' is greater than '2013-01-01' and less than '2016-08-31' 
>>>>,(1, '2016-09-01', '2017-08-31') -- no overlap here
>>>>,(2, '2014-01-01', '2014-05-31') -- this overlaps with the next row
>>>>,(2, '2014-03-01', '2014-12-31') -- this overlaps with the previous row
>>>>
>>>>select * from #Overlaps
>>>>
>>>>drop table #Overlaps

>>>>
>>>>I need a query that will pull out employee 1 having overlaps of dates in the first 2 rows and employee 2's both records overlapping. Note Employee 1's 3rd record is fine.
>>>>
>>>>Basically these are contracts and the start and end date cannot overlap with another contract for the same employee and I need to pull out all the records that do overlap for each employee so they can manually fix them.
>>>>
>>>>This might do it, can anyone see any problems here?
>>>>
>>>>

select o1pk = o1.pk
>>>>		,o1Emp = o1.employeepk
>>>>		,o1Start = o1.startdate 
>>>>		,o1End = o1.Enddate
>>>>		,o2pk = o2.pk
>>>>		,o2emp = o2.employeepk
>>>>		,o2Start = o2.Startdate
>>>>		,o2End = o2.Enddate
>>>>from #Overlaps o1
>>>>	inner join #Overlaps o2 on o1.employeepk = o2.employeepk 
>>>>				and (o1.startdate between o2.startdate and o2.enddate or o1.enddate between o2.startdate and o2.Enddate)
>>>>				and o1.pk <> o2.pk