Selecting records using between operator SQL Server

Question

I am getting confusing in writing the best stored procedure for my requirement.

I am filtering records based on StartDate and EndDate.

So I want to get records whose StartDate and EndDates are between StartDate & EndDate range.

Previously I had asked this question Here in SE, and got some answers. But later I found that the query is not giving me correct results.

So the answer I found there is:

 select * 
 from tbl 
 Where (Event_Startdate between @StartDate and @EndDate) 
   and (Event_Enddate between @StartDate and @EndDate)

Lets say I have records :

enter image description here

If I execute the above query with inputs like:

    @StartDate = '1/1/2000',
    @EndDate = '5/16/2014',

The query above gives no records at all right.. because here Event_StartDate is between the StartDate and EndDate, where as End Date is not between the range.

But what I want is if the StartDate or EndDate is between range, then it should be selected.

Then immediately OR operator came into my mind, I tried by changing And to OR in my query

select * 
from tbl 
Where (Event_Startdate between @StartDate and @EndDate) 
   OR (Event_Enddate between @StartDate and @EndDate)

It gave the correct answer in this situation.

But the problem with this query is it doesn't check the second condition, which is after 'OR' If the first condition is true. Suppose if i gave input as:

    @StartDate = '1/1/2000',
    @EndDate = '7/25/2014'

It will select all records in the table because StartDate is in between 1/1/2000 to 7/25/2014 which is true, so it does nt check whether EndDate is in between the range or not, it does'nt care about EndDate.

I hope my problem is clear to u..Please somebody help with this...

Your query works fine: `OR` is True when at least one of the value is true; unless you want an exclusive `OR` (StartDate between the parameters or EndDate between the parameter, but not both at the same time) — Serpiton, Jul 08 '14 at 07:28
I can't reconcile "what I want is if the StartDate or EndDate is between range, then it should be selected" and then complaining that "It will select all records in the table because StartDate is in between 1/1/2000 to 7/25/2014" If `StartDate` has matched the criteria, then per your first statement, that row **should** be selected. What does `EndDate` have to do with it now? — Damien_The_Unbeliever, Jul 08 '14 at 07:32
The problem is that you are contradicting yourself. You say : "But what I want is if the StartDate or EndDate is between range, then it should be selected." which is exactly what your `OR` query returns, it just so happens that all rows meet the first criteria and some also meet the second but it doesn't matter because they only need to satisfy one criteria. [This Fiddle](http://sqlfiddle.com/#!6/05c40/2) demonstrates this as it is your data set plus one more row which will be selected because the second condition is true but not the first. — Steve Pettifer, Jul 08 '14 at 07:40
Further to @Serpiton's point, if you DO want an exclusive `OR` then [this SO question and accepted answer](http://stackoverflow.com/questions/5411619/t-sql-xor-operator) might help you get there. — Steve Pettifer, Jul 08 '14 at 07:41
Can u check now and let me know, modified according to u r requireement — Azar, Jul 08 '14 at 07:44
There might be confusion in what i said, what i want to say there is: "I want to select the records which are in between the Range(StartDate-EndDate)..sorry if i could not explain well in my question — user3640046, Jul 08 '14 at 07:47
@Raj's answer is quite intersting ..first filtering the records from StartDate, and then again filtering records according to our Daterange...!! — user3640046, Jul 08 '14 at 07:51
Then why should *any* rows be returned when your range is 2000-01-01 to 2014-05-16, as per your first example? — Damien_The_Unbeliever, Jul 08 '14 at 07:55

Azar · Answer 1 · 2014-07-08T07:41:20.627

0

This will work for sure

select * 
from tbl 
Where ( @StartDate between Event_Startdate and Event_Enddate 
        or Event_Startdate between @StartDate and @EndDate) 
 and ( @EndDate between Event_Startdate and Event_Enddate   
       or Event_Enddate between @StartDate and @EndDate)

edited Jul 08 '14 at 07:41

answered Jul 08 '14 at 07:13

Azar

1,852
15
17

Can u check it out and let me know if it works or not – Azar Jul 08 '14 at 07:42

score 0 · Answer 2 · answered Jul 08 '14 at 07:17

0

Try this

select * from (
select * from tbl Where Event_Startdate between @StartDate and @EndDate
) AS A
Where Event_Enddate between @StartDate and @EndDate

answered Jul 08 '14 at 07:17

Raj

10,653
2
45
52

score 0 · Answer 3 · answered Jul 08 '14 at 09:38

If you want rows whose ranges are completely within the bounds of the specified range, specify that Event_Startdate should be not earlier than @StartDate and Event_Enddate not later than @EndDate:

WHERE Event_Startdate >= @StartDate
  AND Event_Enddate   <= @EndDate

score 0 · Answer 4 · answered Dec 09 '14 at 16:40

If You need to filter all overlaping intervals(inner and outer)and optionaly with NULLs in Event_Startdate and Event_Enddate, try this:

select * 
 from tbl 
 Where 
 1=CASE WHEN Event_Startdate IS NOT NULL AND Event_Enddate IS NOT NULL THEN
            CASE WHEN @StartDate<=Event_Startdate AND @EndDate>=Event_Enddate THEN 1
                 WHEN @StartDate>=Event_Startdate AND @EndDate<=Event_Enddate THEN 1
                 WHEN @StartDate<=Event_Startdate AND @EndDate BETWEEN Event_Startdate AND Event_Enddate THEN 1
                 WHEN @EndDate>=Event_Enddate AND @StartDate BETWEEN Event_Startdate AND Event_Enddate THEN 1
                ELSE 0 END
    WHEN Event_Startdate IS NULL AND Event_Enddate IS NOT NULL AND @StartDate<=Event_Enddate THEN 1
    WHEN Event_Enddate IS NULL AND Event_Startdate IS NOT NULL AND @EndDate>=Event_Startdate THEN 1
  ELSE 0 END

score -1 · Answer 5 · answered Jul 08 '14 at 07:14

-1

well you can see in the data:

your date are different.

in the db, it is

 'yyyy/mm/dd'

and your variables are

  'dd/mm/yyyy'

answered Jul 08 '14 at 07:14

Tzook Bar Noy

11,337
14
51
82

it is a minor issue no problem with it is at all – user3640046 Jul 08 '14 at 07:20

Selecting records using between operator SQL Server

5 Answers5