language-agnostic - datetime - 2つの日付範囲が重なっているかどうかの判断

datetime / math

2つの日付範囲が与えられたとき、2つの日付範囲が重なっているかどうかを判断するための最も簡単で効率的な方法は何ですか?

David Faber



Answer #1

Microsoft SQL SERVER で-SQL 関数

CREATE FUNCTION IsOverlapDates 
(
    @startDate1 as datetime,
    @endDate1 as datetime,
    @startDate2 as datetime,
    @endDate2 as datetime
)
RETURNS int
AS
BEGIN
DECLARE @Overlap as int
SET @Overlap = (SELECT CASE WHEN  (
        (@startDate1 BETWEEN @startDate2 AND @endDate2) -内側と終了日の外側に対応
        OR
        (@endDate1 BETWEEN @startDate2 AND @endDate2) -内側と開始日の外側に対応
        OR
        (@startDate2 BETWEEN @startDate1 AND @endDate1) -日付が内側にある外側の範囲に必要なのは1つだけです。
        ) THEN 1 ELSE 0 END
    )
    RETURN @Overlap

END
GO

-上記のコードの実行
DECLARE @startDate1 as datetime
DECLARE @endDate1 as datetime
DECLARE @startDate2 as datetime
DECLARE @endDate2 as datetime
DECLARE @Overlap as int
SET @startDate1 = '2014-06-01 01:00:00' 
SET @endDate1 =   '2014-06-01 02:00:00'
SET @startDate2 = '2014-06-01 01:00:00' 
SET @endDate2 =   '2014-06-01 01:30:00'

SET @Overlap = [dbo].[IsOverlapDates]  (@startDate1, @endDate1, @startDate2, @endDate2)

SELECT Overlap = @Overlap