I have a mysql table which would keep track of drug expiry dates.
SELECT * FROM lm_products
WHERE expiry_date= DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
AND expiry_date<=DATE_SUB(CURDATE(), INTERVAL 1 DAY)
You just need the right comparisons. I think this is the logic:
SELECT * FROM lm_products WHERE expiry_date <= DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
In other words, you want everything whose expiry date is earlier than six months in the future.