How to detect overlap between two dates in SQL?

Question

I have a table as shown below:

EmpId   DateTimeIn                  DateTimeOut                 Effort
1030    2016-12-01 07:30:00.000     2016-12-01 12:30:00.000     1030
1030    2016-12-01 13:30:00.000     2016-12-01 16:30:00.000     1531

The employee 1030invested his efforts in 2016-12-01 for the time ranges from 07:30 to 12:30 and from 13:30 to 16:30.

After that, he should not enter his efforts on these time ranges. He may join the force before 07:30 or from 12:30 to 13:30 or after 16:30 on 2016-12-01 or may enter on any other day.

To do this, I write the query below, but it always throws an error and does not allow users to insert data. Please, help.

IF EXISTS (SELECT 1 FROM TimesheetEntries 
WHERE EmpId = @EmpId AND (@DateTimeIn >= DateTimeIn AND @DateTimeIn < DateTimeOut)  
OR (@DateTimeOut >= DateTimeOut AND @DateTimeOut < DateTimeIn))
BEGIN
    RAISERROR ('You already input your effort for the given time range.',16,1)
    RETURN 
END

Answer 1

, - , , - , , .

IF EXISTS (SELECT 1 FROM TimesheetEntries 
WHERE EmpId = @EmpId AND @DateTimeIn  < DateTimeOut  
AND @DateTimeOut > DateTimeIn)   --has to have started before end AND  ended after the start
BEGIN
RAISERROR ('You already input your effort for the given time range.',16,1)
RETURN 
END

Answer 2

- AND OR EmpId = @EmpId. , ( , @DateTimeOut DateTimeOut DateTimeIn, DateTimeOut > DateTimeIn, , ).

Try:

IF EXISTS 
(
    SELECT 1 
    FROM TimesheetEntries 
    WHERE EmpId = @EmpId 
    AND ((@DateTimeIn >= DateTimeIn AND @DateTimeIn <= DateTimeOut)  OR (@DateTimeOut >= DateTimeIn AND @DateTimeOut <= DateTimeOut))
)
BEGIN
    RAISERROR ('You already input your effort for the given time range.',16,1)
    RETURN 
END

Answer 3

.

: @3N1GM4 , .

IF EXISTS 
(
    select TOP 1 1 from HR.TimesheetEntries where SubmittedByEmpId = 1030 AND 
(
(DateTimeIn > @DateTimeIn AND DateTimeIn < @DateTimeIn) OR (DateTimeOut > @DateTimeOut AND DateTimeOut < @DateTimeOut) OR
(DateTimeIn > @DateTimeIn AND DateTimeOut < @DateTimeIn) OR (DateTimeOut > @DateTimeOut AND DateTimeIn < @DateTimeOut) OR
(DateTimeOut > @DateTimeIn AND DateTimeIn < @DateTimeIn) OR (DateTimeIn > @DateTimeOut AND DateTimeOut < @DateTimeOut) OR
(DateTimeOut > @DateTimeIn AND DateTimeOut < @DateTimeIn) OR (DateTimeOut > @DateTimeOut AND DateTimeOut < @DateTimeOut)
)
)
BEGIN
    RAISERROR ('You already input your effort for the given time range.',16,1)
    RETURN 
END