MySQL Date Comparison

I have a table with subscription information. I am trying to get it to return results where the date is within a certain range, the range being the date of subscription, plus x years, where x is a field in each row.

I have the following fields, (plus more but i removed to keep easy) and I want to extract all expired memberships. I have used the following statement, however it doesnt take into account the days of the month, and returns fields that are going to expire that month.

Is this even possible? Or do I have to filter results with php?


sponsorshipID | sponsorID | start_date | years | ...


sponseeID | lname | fname |...

SQL Query:

SELECT  t1.fname,t1.lname,t1.sponseeID,t2.*
FROM 2b_sponsees as t1
INNER JOIN 2b_sponsorships as t2
ON t2.sponseeID = t1.sponseeID
WHERE t2.sponsorID = 4 AND (YEAR(CURDATE())-YEAR(t2.start_date))-((MID(CURDATE(),6,2)< MID(t2.start_date,6,2) && (MID(CURDATE(),6,2)< MID(t2.start_date,6,2)) >= t2.years AND t2.auth = 1
ORDER BY t2.start_date ASC
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Try like this

FROM ...
WHERE DATE_ADD(t2.start_date, INTERVAL t2.years YEAR) >= CURDATE();

so selected would be all dates that are valid (start_date + years) >= current date


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Since you are looking for expired membership you might just want to change >= to < so try,

SELECT  t1.fname,t1.lname,t1.sponseeID,t2.*
FROM 2b_sponsees as t1
INNER JOIN 2b_sponsorships as t2
ON t2.sponseeID = t1.sponseeID
WHERE t2.sponsorID = 4 AND DATE_ADD(t2.start_date, INTERVAL t2.years YEAR) < CURDATE() AND t2.auth = 1
ORDER BY t2.start_date ASC
m3_malAuthor Commented:
Awesome, work a treat. Thanks the both of you, I was using a similar query to figure out the non expired sponsorships as well so the >= & < mistake would have been figured out anyway. Thanks the both of you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.