Mysql date range query returning wrong set of results

Question

Here is the query:

SELECT `mainevents`.`maineventid`   AS `MainEventID`, 
       `mainevents`.`maineventcode` AS `MainEventCode`, 
       `mainevents`.`datestart`     AS `DateStart`, 
       `mainevents`.`dateend`       AS `DateEnd`, 
       `mainevents`.`location`      AS `Location`, 
       `mainevents`.`name`          AS `Name` 
FROM   `mainevents`, 
       `mainevents_lookup` 
WHERE  `mainevents`.`maineventid` = `mainevents_lookup`.`maineventid` 
       AND `mainevents`.`categoryid` = 1 
       AND ( ( `mainevents_lookup`.`datestart` >= '2016-02-27 00:00:00' 
               AND `mainevents_lookup`.`datestart` <= '2016-05-27 11:59:59' ) 
              OR ( `mainevents_lookup`.`dateend` >= '2016-02-27 00:00:00' 
                   AND `mainevents_lookup`.`dateend` <= '2016-05-27 11:59:59' ) 
              OR ( `mainevents_lookup`.`dateend` <= '2016-05-27 11:59:59' 
                   AND `mainevents_lookup`.`datestart` >= '2016-02-27 00:00:00' 
                 ) ) 
LIMIT  0, 30

It is trying to find the listings that have dates that fall within the selected two date ranges. DateStart is '2016-02-27 00:00:00' and DateEnd is '2016-05-27 11:59:59'

For some reason it keeps giving me the opposite results of what I want to find. I know the answer is right in front of me, anyone can give me a hand?

Why you didnt use BETWEEN also show us current sample data and expected result. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 27, 2016 at 17:55
@Dave That looks like should be an answer so you could format it properly. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 27, 2016 at 18:02

Community · Accepted Answer · 2017-05-23 11:58:42Z

1

Check Overlaping date ranges (StartA <= EndB) and (EndA >= StartB)

SELECT `mainevents`.`maineventid`   AS `MainEventID`, 
       `mainevents`.`maineventcode` AS `MainEventCode`, 
       `mainevents`.`datestart`     AS `DateStart`, 
       `mainevents`.`dateend`       AS `DateEnd`, 
       `mainevents`.`location`      AS `Location`, 
       `mainevents`.`name`          AS `Name` 
FROM   `mainevents`, 
       `mainevents_lookup` 
WHERE  `mainevents`.`maineventid` = `mainevents_lookup`.`maineventid` 
  AND `mainevents`.`categoryid` = 1 
  AND `mainevents_lookup`.`datestart` <= '2016-05-27 11:59:59'     
  AND `mainevents_lookup`.`dateend` >= '2016-02-27 00:00:00'
LIMIT  0, 30

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered May 27, 2016 at 18:01

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave · Accepted Answer · 2016-05-27 18:03:34Z

1

The 2nd OR condition should not be needed and it looks like the last AND test is using the wrong column (datestart instead of dateend).

AND ( ( `mainevents_lookup`.`datestart` >= '2016-02-27 00:00:00' 
           AND `mainevents_lookup`.`datestart` <= '2016-05-27 11:59:59' ) 
          OR ( `mainevents_lookup`.`dateend` >= '2016-02-27 00:00:00' 
               AND `mainevents_lookup`.`dateend` <= '2016-05-27 11:59:59' ) 
)

answered May 27, 2016 at 18:03

Dave

5,19717 gold badges34 silver badges45 bronze badges

Comments

Scott Alan Kline · Accepted Answer · 2016-05-27 18:13:32Z

0

I should have done the default and validated the initial data. The data in the table was incorrect, the query was correctly pulling the future event as the date range in the lookup table was within the queried date ranges.

answered May 27, 2016 at 18:13

Scott Alan Kline

1

Collectives™ on Stack Overflow

Mysql date range query returning wrong set of results

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related