>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.

>>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