两端时间比较有如下几种情况:
相交
-------------------------1 ------------------------ A. |---------------| B. |----------------------| -------------------------2 ------------------------ A. |------------------------| B. |-------------------------| -------------------------3------------------------ A. |------------------------| B. |---------------------------------| -------------------------4------------------------ A. |------------------------| B. |------------------|
不相交
-------------------------1 ------------------------ A. |---------------| B. |----------------------| -------------------------2 ------------------------ A. |------------------------| B. |------------|
实际判断是不需要6种情况逐个判断,
首先判断哪个时间段在前面,然后比较后面的时间段的起点和靠前时间的结束时间比较即可:
CREATE FUNCTION [dbo].[fn_CheckDateRangeOverlap]
(
@Date1Begin DATETIME,
@Date1End DATETIME,
@Date2Begin DATETIME,
@Date2End DATETIME
)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @iRet BIT
DECLARE @LastDateBegin DATETIME,@EarlyDateEnd DATETIME
SET @EarlyDateEnd=CASE WHEN DATEDIFF(d,@Date1Begin,@Date2Begin)>=0 THEN @Date1End ELSE @Date2End END
SET @LastDateBegin=CASE WHEN DATEDIFF(d,@Date1Begin,@Date2Begin)>=0 THEN @Date2Begin ELSE @Date1Begin END
SET @iRet=CASE WHEN DATEDIFF(d,@LastDateBegin,@EarlyDateEnd)>0 THEN 1 ELSE 0 END
RETURN @iRet
END