IF EXISTS (SELECT 'x' FROM sysobjects WHERE name = 'OverlapDates')
DROP FUNCTION OverlapDates
GO
CREATE FUNCTION OverlapDates
(@Startdate as datetime,
@EndDate as datetime = null,
@Startdate2 as datetime,
@EndDate2 as datetime = null)
RETURNS bit
AS
BEGIN
Declare @ReturnValue as bit
/*
Author: Philip Leitch
Date: 2005
Purpose: Determines if one pair of dates overlaps with another pair of dates.
Copyright: Philip Leitch 2005
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:
This function assumes that the start is not null in either pair but the end date can be null of either pair.
The start and end date are common for any duration based activity, such as pricing or activities.
The ranges overlap if the first start is before the second start,
and the first end is after the second start.
OR
The ranges overlap if the first start is betwene the second start and the second end.
*/
if (@Startdate <= @Startdate2 and (@Startdate2 <= @EndDate or @EndDate is null))
or
(@Startdate > @Startdate2 and (@Startdate <= @EndDate2 or @EndDate2 is null))
set @ReturnValue = 1
else
set @ReturnValue = 0
return @ReturnValue
End
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.OverlapDates('1/1/2003', getdate(), '1/1/05','')