CODEWITHSUNDEEP
mysql
A K
A K

Reputation: 11

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?

Upvotes: 0

Views: 1485

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94642

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

Upvotes: 0

Takehana
Takehana

Reputation: 74

Try to use full datetime with DATE_ADD and DATE_DIFF functions.

select * from dateTable where t2.`END_DATE` 
between DATEADD(month, 
        DATEDIFF(month, 0, getdate() -- get difference to first day
        ), 0) 
and DATEADD(month, 2, -- add 3 months interval to get first day of third
       DATEADD(month, 
          DATEDIFF(month, 0, getdate() -- get difference to first day
        ), 0))

If you have to remove the first day from last month use DATEADD again and remove 1 second to get 23:59:59

Upvotes: 0

Related Questions