A K A K - 22 days ago 4
MySQL Question

How do i select all records for the next 3 months including the current one when it turns into a new year

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.

For ex.

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 returns 0 rows because this cannot handle having two years, 2016 and 2017.

How would I go about doing this?

Answer

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
Comments