CREATE FUNCTION OverlapDates
    (@Startdate as datetime= null,
    @EndDate as datetime = null,
    @Startdate2 as datetime= null,
    @EndDate2 as datetime = null)
RETURNS bit
AS
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2009
Purpose: This function determines whether pairs of dates overlap or not.
Copyright: Philip Leitch 2009
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
The ranges overlap if the first start or end is between the start and end of the second time range.
The ranges overlap i the second start or end is between the start and end of the first time range.
The "order" of start/finish is assumed to be in advancing time, nulls are considered as "N/A". 

For instance, does my life overlap with another person's life.  if we are both still alive then we both
have no end date defined because it hasn't happened yet.  Therefore we automatically overlap. 
*/
BEGIN
    Declare @ReturnValue as bit

    if (    @Startdate Between  isnull(@Startdate2,@Startdate-1) and isnull(@EndDate2, @startdate+1)
        or
        @EndDate Between  isnull(@Startdate2,@EndDate-1) and isnull(@EndDate2, @EndDate+1)
        or
        @Startdate2 Between  isnull(@Startdate,@Startdate2-1) and isnull(@EndDate, @Startdate2+1)
        or
        @EndDate2 Between  isnull(@Startdate,@EndDate-1) and isnull(@EndDate, @EndDate+1)
    )
        set @ReturnValue = 1
    else
        set @ReturnValue = 0
    return @ReturnValue
End
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.OverlapDates('1/1/1999', '1/1/2000', '1/1/2002','1/1/2008'), 'Standard ranges not overlapping, first earlier than second'
union all SELECT dbo.OverlapDates('1/1/02','1/1/2008', '1/1/1999', '1/1/2000'), 'Standard ranges not overlapping, first later than second'
union all SELECT dbo.OverlapDates('1/1/1999', '1/1/2008', '1/1/2002','1/1/2003'), 'Standard ranges overlapping'
union all SELECT dbo.OverlapDates('1/1/2003', null, '1/1/05',null) As Result, 'Second range higher than first, neither have a known end' as Test
union all SELECT dbo.OverlapDates('1/1/2003', null, '1/1/01',null), 'Second range lower than first, nether have a known end'
union all SELECT dbo.OverlapDates('1/1/2003', '1/1/2004', '1/1/01',null), 'Second range started before first but without an end'
union all SELECT dbo.OverlapDates('1/1/2003', null, null,'1/5/2005'), 'Second range ended after first started.  First range has no end, second range no beginning'
union all SELECT dbo.OverlapDates(null, '1/1/2000', '1/1/02',null), 'Second range starts but has no known end, second range has no known start but ends before the start of the second range'