How To Compare Two Date Fields With the Two Date Variables

Question

Hi, I have one table as Follows

ID        bigint
TaskName  nvarchar(50)
StartDate datetime      -- Start date of task
EndDate   datetime      -- End Date Of Task

I am trying to Write One Procedure Which Takes Two inputs @FromDate and @EndDate as datetime, To get list of TaskNames Which are in process between input parameters.

Can any body help me to write the Where condition for it

Thanks And Regards, Rohan

Answer 1

Something like the following should work for you:

select * from myTable
where StartDate > @FromDate
and EndDate < @EndDate

Or something like

select * from myTable
where StartDate between @FromDate and @EndDate

The first one gets all tasks that are in between your from and end dates. The second one gets all tasks that were started (regardless of end time) between your from and end dates.

Answer 2

you don't specify if you want to consider time or include endpoints, but I'm not going to write out each possible combination... this considers time and allows for inclusion of end points:

SELECT
    *
    FROM YourTable
    WHERE StartDate>=@StartDate AND EndDate<=@EndDate

Answer 3

WHERE (StartDate < @Fromdate ) AND ( EndDate > @EndDate )

will give the tasks that started earlier than @FromDate but not finished before @EndDate.

Answer 4

WHERE StartDate <= @EndDate AND EndDate >= @FromDate

Should do the trick

Edit: Sample code I used to test various scenarios:

DECLARE @Data TABLE (StartDate DATETIME, EndDate DATETIME, ID INTEGER)
INSERT @Data 
SELECT '2009-05-10','2009-05-20', 1
UNION
SELECT '2009-05-15','2009-05-20', 2
UNION 
SELECT '2009-05-01','2009-06-01', 3

DECLARE @FromDate DATETIME
DECLARE @EndDate DATETIME
SET @FromDate = '2009-05-11'
SET @EndDate = '2009-05-18'

SELECT * FROM @Data
WHERE StartDate <= @EndDate AND EndDate >= @FromDate

Answer 5

You could do this.

Create procedure sp_name
(@fromdate as datetime,
@Enddate as datetime
) 
as

select * from tablename
where StartDate between @FromDate and @EndDate

Answer 6

UPDATE: After a good discussion with Ada, I see how his query is the optimal solution. While my answer below will work, it's redundant. I'll leave this answer here for history's sake. +1 to Ada's answer.

As I understand the question, he wants to retrieve any tasks that were being worked on at any point between the dates specified. This implies partially overlapping tasks as well as tasks which started and finished during the time interval.

Two time slots overlap if:

• The start time on task 1 falls anywhere between the start and end times of task 2 OR
• The end time on task 1 falls anywhere between the start and end times of task 2 OR
• Start and end times of

I have used this method successfully in a reservations system to prevent any overlap in bookings.

So far in the answers I haven't seen a query that checks both of these conditions. Here's my attempt:

WHERE ( (StartDate >= @FromDate) AND (StartDate <= @EndDate) )
OR ( (EndDate >= @FromDate) AND (EndDate <= @EndDate) )
OR ( (StartDate <= @EndDate) AND (EndDate >= @StartDate) )