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 <> 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 =EndDate
)
)
AND kUserID = @UserID;


If Count(ID) > 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 lea

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 > StartDate and @Start StartDate and @End EndDate)
)


StackExchange Database Administrators Q#111426, answer score: 2