I am trying to select every record between current month and the next 2 months but I am not able to because the year will be changing from 2016 to 2017.
I want to get all the records from November 2016 to January 2017.
The current query (shown below) i have has worked fine until this month because November 2016 + 2 months = Jan 2017.
select * from dateTable
where month(t2.`END_DATE`) between month(curdate()) and
month(DATE_ADD(curdate(), INTERVAL 2 MONTH))
and year(t2.`END_DATE`) = year(curdate());
This should be what you need, although there are probably a number of ways of doing this
select * from dateTable where `END_DATE` BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND LAST_DAY(DATE_ADD(NOW(), INTERVAL 2 MONTH))
The result of this query will demonstrate the dates being generated
SELECT DATE_FORMAT(NOW() ,'%Y-%m-01') as from_date, LAST_DAY(DATE_ADD(NOW(), INTERVAL 2 MONTH)) as to_date
Today this will generate
from_date to_date 2016-11-01 2017-01-31