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**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 27 '16 at 17:55
@Dave That looks like should be an answer so you could format it properly. — Juan Carlos Oropeza, May 27 '16 at 18:02

score 1 · Answer 1 · edited May 23 '17 at 11:58

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

score 1 · Answer 2 · answered May 27 '16 at 18:03

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' ) 
)

score 0 · Answer 3 · answered May 27 '16 at 18:13

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 '16 at 18:13

Scott Alan Kline

1

Mysql date range query returning wrong set of results

3 Answers3