Hanner Hanner - 2 months ago 6
MySQL Question

Select all previous dates and select all dates within 6 months using mysql

I have a mysql table which would keep track of drug expiry dates.

DrugExpiry table:

drugid, expiry_date
3 2016-08-10
2 2016-10-12


Now I want to select all expired drugs and all the drugs that are going to expire within six months.

I have tried the following select, but it does not work as expected.

SELECT * FROM lm_products
WHERE expiry_date= DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
AND expiry_date<=DATE_SUB(CURDATE(), INTERVAL 1 DAY)


This select returns an empty row. Where do I go wrong? Please help.

Answer

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.

Comments