How to get in between dates from table using MYSQL

Question

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..

Dan Grossman · Accepted Answer · 2011-02-04 05:33:59Z

3

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'

answered Feb 4, 2011 at 5:33

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

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

Collectives™ on Stack Overflow

How to get in between dates from table using MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related