current month with rolling previous 6 months in mysql

I am trying to report numbers back rolled up by month for the past 6 months based off of the current_date() field.  I tried using date_add(month(current_date()),INTERVAL -6 MONTH) but that does not work.  

I did have date_add(current_date(),INTERVAL -6 MONTH) in place but that is not totally accurate because if i run the query in the second week of the month then the first month (october) will only have numbers for the remaining two weeks.  I need it by month.
szadrogaAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
This will provide an accurate date range filter for the past 6 months.
select * from table1
where  `dt` >= date_add(date_add(LAST_DAY(current_date()), INTERVAL 1 DAY), INTERVAL -6 MONTH)
and `dt` < date_add(LAST_DAY(current_date()), INTERVAL 1 DAY) 

Open in new window


Note this does NOT use between deliberately. See "Beware of Between"

nb: this assumes you want "whole months" only including the current month. You mention October however (In April October 7 months ago).
Do you want to exclude the current month?
0
 
szadrogaAuthor Commented:
the LAST_DAY() did the trick. thx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.