CODEWITHSUNDEEP
mysql
zulubanshee
zulubanshee

Reputation:

How can I query from 2 dates ranges (mysql)

I want to pull the emails from 40 days ago and 20 days ago, each range being 1 day. This is what I have but it returns no rows. 

SELECT s.*, r.email FROM sellyourcar s 
     INNER JOIN register r ON s.rid = r.slno 
     WHERE s.dt BETWEEN DATE_SUB(curdate(),INTERVAL 20 DAY) AND DATE_SUB(curdate(), INTERVAL 19 DAY) AND 
     s.t BETWEEN DATE_SUB(curdate(),INTERVAL 40 DAY) AND DATE_SUB(curdate(), INTERVAL 39 DAY)

Upvotes: 0

Views: 404

Answers (1)

Jacob Schoen
Jacob Schoen

Reputation: 14212

You should be using an OR instead of and for your two intervals:

SELECT s.*, r.email 
FROM sellyourcar s INNER JOIN register r ON s.rid = r.slno 
WHERE s.dt BETWEEN DATE_SUB(curdate(),INTERVAL 20 DAY) AND DATE_SUB(curdate(), INTERVAL 19 DAY) 
OR s.dt BETWEEN DATE_SUB(curdate(),INTERVAL 40 DAY) AND DATE_SUB(curdate(), INTERVAL 39 DAY)

As another note, at least in Oracle, I am not sure about in MYSQL the BETWEEN functions returns items matching the end items also. For example between 3 and 5 would also return items that were 3 and 5 and not just 4. So you may want to just to check if the date of the email is 20 or 40 days less than the current date.

Upvotes: 2

Related Questions