(Expiring Next Month) Problem
-
Hi, I have expiry_date field. I am using nthis code to get expired this month list.
SELECT contracts.contract_id, contract_types.type_name, contracts.contract_name, contracts.supplier, contracts.contact_name, contracts.contact_number, contracts.expiry_date, contracts.reminder FROM contracts
JOIN contract_types ON contract_types.type_id = contracts.type
WHERE MONTH(contracts.expiry_date) = MONTH(NOW()) AND YEAR(contracts.expiry_date) = YEAR(NOW());Now, I would like to get list of expiring next month. How can I do this? what if current month is 12? then Next Month will be 1 and Year will also be changed? Kindly advise
Technology News @ www.JassimRahma.com
-
Hi, I have expiry_date field. I am using nthis code to get expired this month list.
SELECT contracts.contract_id, contract_types.type_name, contracts.contract_name, contracts.supplier, contracts.contact_name, contracts.contact_number, contracts.expiry_date, contracts.reminder FROM contracts
JOIN contract_types ON contract_types.type_id = contracts.type
WHERE MONTH(contracts.expiry_date) = MONTH(NOW()) AND YEAR(contracts.expiry_date) = YEAR(NOW());Now, I would like to get list of expiring next month. How can I do this? what if current month is 12? then Next Month will be 1 and Year will also be changed? Kindly advise
Technology News @ www.JassimRahma.com
WHERE MONTH(contracts.expiry_date) = DATE_ADD(MONTH(NOW()), INTERVAL 1 MONTH)
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
Hi, I have expiry_date field. I am using nthis code to get expired this month list.
SELECT contracts.contract_id, contract_types.type_name, contracts.contract_name, contracts.supplier, contracts.contact_name, contracts.contact_number, contracts.expiry_date, contracts.reminder FROM contracts
JOIN contract_types ON contract_types.type_id = contracts.type
WHERE MONTH(contracts.expiry_date) = MONTH(NOW()) AND YEAR(contracts.expiry_date) = YEAR(NOW());Now, I would like to get list of expiring next month. How can I do this? what if current month is 12? then Next Month will be 1 and Year will also be changed? Kindly advise
Technology News @ www.JassimRahma.com
Have a look at the
DATEDIFFfunction. E.g.select datediff(mm,getdate(),getdate()+180)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
WHERE MONTH(contracts.expiry_date) = DATE_ADD(MONTH(NOW()), INTERVAL 1 MONTH)
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
this solved my problem.
WHERE MONTH(contracts.expiry_date) = MONTH(DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH)) AND YEAR(contracts.expiry_date) = YEAR(DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH));
Thank you so much
Technology News @ www.JassimRahma.com
-
this solved my problem.
WHERE MONTH(contracts.expiry_date) = MONTH(DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH)) AND YEAR(contracts.expiry_date) = YEAR(DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH));
Thank you so much
Technology News @ www.JassimRahma.com
This may be easier to read and you won't need to do calculations on years:
WHERE DATEDIFF(mm,contracts.expiry_date,DATE_ADD(NOW(), INTERVAL 1 MONTH)) = 1
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
This may be easier to read and you won't need to do calculations on years:
WHERE DATEDIFF(mm,contracts.expiry_date,DATE_ADD(NOW(), INTERVAL 1 MONTH)) = 1
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Hm, depends on the details of his requirements. If I see it correctly, your query will select all data within one month from now. His question looked like he needed the data of next calendar month.
-
Hm, depends on the details of his requirements. If I see it correctly, your query will select all data within one month from now. His question looked like he needed the data of next calendar month.
Try the query out - you will find that it selects the data from next calendar month ;)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
