How to get in between dates from table using MYSQL

0
votes

I am a table with three fields: id, startDate and endDate.

Now i need to get the records exactly in between the startDate and endDate.

For example let us assume i am having the table with the following records:

id startDate  endDate

1  2011-02-05 2011-02-10

2  2011-02-01 2011-02-06

3  2011-02-06 2011-02-08

For example startDate is 2011-02-05 and the end date is 2011-02-10 the output should be the following

id startDate  endDate

1  2011-02-05 2011-02-10

3  2011-02-06 2011-02-08

But according to my query second record is also appearing in the output. actually it should not...

My query is:

SELECT * FROM tblname WHERE startDate AND expiryDate BETWEEN '2011-02-05' AND '2011-02-10'

How to resolve this issue?

Thanks in advance..

1
mysql

1 Answers

3
votes

You can't compare two columns to a range like that. The AND and OR operators connect boolean expressions. Your query is interpreted as "WHERE startDate is boolean true AND expiryDate is between these dates".

SELECT 
  * 
FROM 
  tblname 
WHERE 
  startDate BETWEEN '2011-02-05' AND '2011-02-10' 
AND 
  expiryDate BETWEEN '2011-02-05' AND '2011-02-10'