Mysql query between 3 date ranges
I have a PHP script that allows the user to create reports, they have the option to select 3 date ranges. i.e.
2012-01-01 to 2012-01-31
AND
2011-01-01 to 2011-01-31
AND
2010-01-01 to 2010-01-31
But when I execute my query and there are no records between the specified date range, the query does not return any values.
Here is my request:
SELECT DealerName WHERE InterviewDate >= "2012/01/01 " AND InterviewDate <= " 2012/03/31"
AND InterviewDate >= "2012/07/01 " AND InterviewDate <= " 2012/09/31"
I need a query that will return data if it is found in 1 of the date ranges, regardless of whether there is data in subsequent date ranges.
The data is summarized into a table in which one could have the same data on several dates.
eg:
dealerName 1 2012-01-01
dealerName 1 2012-02-31
dealerName 2 2012-03-01
dealerName 1 2012-06-01
dealerName 1 2012-06-31
So, if I wanted to query data between 2012-01-01 and 2012-02-31 AND 2012-06-01 AND 2012-06-31. I understand that 1 date range can span all of these ranges, but the user might want to exclude data between these two date ranges.
source to share
The interview date cannot be between x and y AND is still between z and zz. You are missing OR
- Also look at the operatorBETWEEN
SELECT DealerName
FROM table
WHERE InterviewDate BETWEEN '2012-01-01' AND '2012-01-31'
OR InterviewDate BETWEEN '2011-01-01' AND '2011-01-31'
OR InterviewDate BETWEEN '2010-01-01' AND '2010-01-31'
edit, sorry, missed the table name :)
source to share
You need to use OR
insteadAND
WHERE (InterviewDate BETWEEN '2012-01-01' AND '2012-01-31')
OR
(InterviewDate BETWEEN '2011-01-01' AND '2011-01-31')
OR
(InterviewDate BETWEEN '2010-01-01' AND '2010-01-31')
UPDATE 1
I think you have a problem Relational Division
,
SELECT name
FROM table1
WHERE DATE BETWEEN '2012-01-01' AND '2012-02-31'
OR
DATE BETWEEN '2012-06-01' AND '2012-06-31'
GROUP BY name
HAVING COUNT(*) = 2
source to share
Here is my fence :) since you are checking month
January for year
2010, 2011, 2012. We can use month
and year
.
SELECT DealerName
FROM yourtable
WHERE Month(InterviewDate) = 1
AND Year(InterviewDate) IN (2010, 2011, 2012)
;
SELECT DealerName
FROM yourtable
WHERE Month(InterviewDate) IN (1, 2) // if there are particular months
AND Year(InterviewDate) IN (2010, 2011, 2012)
;
source to share