I have a given date:
2013-12-20
This is an issue date of a product where the product expiries in 6 months from the date of issue. The expiration date it is not stored so I want to check from the given issue date if it is expiring in the next month of now().
So my guess would be to add 6 months to the issue date and check now + interval of 1 month if it is expiring?
Here is what I tried:
SELECT * FROM `products` WHERE DATE_ADD(`issue_date`,INTERVAL 6 MONTH)
< DATE(NOW() + INTERVAL 1 MONTH) ORDER BY `issue_date` ASC
What I'm I doing wrong?
here's a suggestions which might help
$date = date("y-m-d", strtotime(" +6 months"));
this will result in date 6 months later from current date:
similarly,
for one month from current date
$date = date("y-m-d", strtotime(" +1 months"));
SELECT * FROM `products`
WHERE NOW() >= DATE_ADD(`issue_date`,INTERVAL 5 MONTH)
AND NOW() <= DATE_ADD(`issue_date`,INTERVAL 6 MONTH)
ORDER BY `issue_date` ASC
Your query says to select all the products that will expire within a month or had already expired. You could use between clause
SELECT * FROM `products` WHERE DATE_ADD(`issue_date`,INTERVAL 6 MONTH) between NOW() and DATE(NOW() + INTERVAL 1 MONTH) ORDER BY `issue_date` ASC
If you want to check if the product expiries in 6 months from the date of issue and if it is expiring in the next month, the it is better to add this 1 month to the 6th in the query:
SELECT * FROM products WHERE issue_date <= DATE_ADD(issue_date,INTERVAL 7 MONTH)
ORDER BY issue_date ASC;
try this
SELECT * FROM `products` where DATE_ADD(`issue_date`,INTERVAL 6 MONTH) < CURDATE()
After a few of these answers I ended up working it out with this:
SELECT * FROM `products`
WHERE DATE_ADD( `issue_date` , INTERVAL 6 MONTH )
< DATE(NOW() + INTERVAL 1 MONTH)
SELECT * FROM products WHERE Now() <= DATE_ADD(issue_date, INTERVAL 7 MONTH) && Now() >= DATE_ADD(issue_date, INTERVAL 6 MONTH) ORDER BY issue_date ASC;