Detect if two time periods overlap I have an HR system and I need to detect if a new leave (time off) request conflicts with an existing one. I have written code to do this based on the entire day; I now need to do the comparison based on half days.



Here is my data structure (and the data format). I am using SQL Server 2008 R2.

StartDate     (datetime)
EndDate       (datetime)


The following 2 variables show if the user is taking a half or a full day off. 
The options for each are listed and explained.

StartDateAMPM (int) 0=All Day (StartDate may or may not be the same as the EndDate), 
                    1=AM, (User is out of the office only in the morning)
                    2=PM  (User is out of the office only in the afternoon)

EndDateAMPM   (int) 0="All Day" / "half day" / "StartDate = EndDate", 
                    1=AM (The user is off for the morning only)

**NOTE This cannot be PM (as you cannot end a leave with a half day in the afternoon)

**IF this is a "0", there are 3 possibilities --
A) a full day leave where the StartDate &lt;&gt; EndDate
B) a full day where the StartDate = EndDate
C) a partial day where StartDate = EndDate 
   and the user is off in the morning or the afternoon


I pass this info to the database and am trying to see if the new leave conflicts with existing leave. Here is the code to do it for a single day:

Legend:  @Start DateTime -- StartDate from new leave
         @end   DateTime -- EndDate from new leave
         @StartDateAMPM (int) 0,1,2 (see above for details)
         EndDateAMPM    (int) 0,1   (see above for details)


select count(ID)  
from v_VacReqWHalfDays 
where 
(
        @Start BETWEEN StartDate and EndDate 
    OR  @End   BETWEEN StartDate and EndDate 
    OR  
    (
        @Start &lt;= StartDate 
        AND @End &gt;=EndDate
    )
)
AND kUserID = @UserID;


If Count(ID) &gt; 0, there is a conflict.

My question is how to incorporate the 1/2 day leave. People can take full days or half days off (the morning or the afternoon).

I want to use TSQL to determine if the current leave request conflicts (overlaps) with an existing and can't figure out how to do it.
I realize this has been answered before on Stack Overflow. It got me going in the right direction.  

I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering.

For anyone in the future who has a similar issue:


My new StartDate will be recorded as 08/22/2015 09:00.000 instead of 08/22/2015 00:00.000
The EndDate will be 8/23/2015 13:00:00.000


I can then compare as per the linked answer:

(
    (@Start &gt; StartDate and @Start &lt; EndDate)
    OR (@End &gt; StartDate and @End &lt; EndDate)
    OR (@Start &lt; StartDate and @End &gt;EndDate)
)