DATE_ADD with OR adds 2 years?

[developerdave]

So I'm having a bit of an issue with a query I'm writing, my table has 2 colulmns with date types and I'm trying to select where either of these dates are between now and 90 days ahead (3 months) but I'm having an issue where it seems to be selecting between now and 2 years ahead.

 

below is my query

 

SELECT * FROM `main_table`
WHERE `e_archive` != 1
AND (`date_one` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)
OR (`date_two` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)))
ORDER BY `date_one` ASC

 

The issue is around the OR statement between the 2 BETWEEN queries, if I change it to and I get the right dates but the wrong data returned obviously.

 

Any help is greatly appreciated guys,

Dave

[gizmola]

When you say it "seems to be" that's where you probably don't realize that you didn't provide us any information about the result set. 

 

I will go ahead and state the obvious then, that if you use OR, the condition simply needs to be valid on either date_one or date_two for it to come back in the result set.  Since you ORDER BY date_one at the end, this might explain your confusion, because a row could match the date_two but have a date_one that is far in the future.  That's the nature of the query you have created.

[developerdave]

Wow, is it just me or have the moderators got a lot more rude than they used to be? 

 

Anyways, I did say that the data set I'm getting returned is completely out of the range I have specified in the query, I tried taking out my order by before as I thought the same but it didn't make the slightest bit of difference. I have just stripped down my query and I've managed to get it selecting the right data set (nearly)

 

This is what I've ended up with so I will have to check the rest of my query.

 

SELECT * FROM `main_table`
WHERE (
`date_one` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)
OR `date_two` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY)
)

 

Thanks

[gizmola]

There was nothing rude about my reply.  Since you misunderstood what I wrote, perhaps you also misunderstood what I wrote about the ORDER BY.  I did not say that the order by could effect the result set.  Only that your use of it might lead to your perception that something is wrong. 

 

You also missed the main point which is, that you continue to post sql and neglect to post in data from the result set that shows what you mean when you claim it is out of range.  A describe of your main_table wouldn't hurt either.

 

Feel free to google mysql curdate if you are interested, as an article I wrote almost 7 years ago about using it with DATE_ADD is still in the first page of results, so I would have to say this is an area of mysql functionality I understand better than most