Querying table with 2 date ranges

Forum Moderators: open

Message Too Old, No Replies

Querying table with 2 date ranges

zulubanshee

1:53 am on Jan 23, 2009 (gmt 0)

I would like my query to be able to select from 2 date ranges, one from 20 days ago and one from 40 days ago (mysql). Here is what I have:

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

It works with one date range, but not 2.

zulubanshee

2:15 am on Jan 23, 2009 (gmt 0)

Well now i see why that's wrong, but I don't see how to do it right.

LifeinAsia

4:56 pm on Jan 23, 2009 (gmt 0)

Well now i see why that's wrong, but I don't see how to do it right.

Um, what exactly ARE you trying to do? Do you mean that you are trying to find out if the datetime field (which, by the way, is not a good name for a field) is 20 days ago OR 40 days ago? If so, try:
SELECT s.*, r.email
FROM sellyourcar s INNER JOIN register r ON s.rid = r.slno
WHERE (s.datetime BETWEEN DATE_SUB(curdate(),INTERVAL 20 DAY) AND(DATE_SUB(curdate(), INTERVAL 19 DAY))
OR (s.datetime BETWEEN DATE_SUB(curdate(),INTERVAL 40 DAY) AND DATE_SUB(curdate(), INTERVAL 39 DAY))

[edited by: LifeinAsia at 4:57 pm (utc) on Jan. 23, 2009]

zulubanshee

5:42 pm on Jan 23, 2009 (gmt 0)

I'm sorry for not being more clear. Your suggestion is correct, by the way and I thank you. In case anyone is interested, register is the table with customer info. sellyourcar is the table with their orders. s.datetime is when they placed an order.

This was a silly mistake on my part. I should have spotted the problem earlier.