Find last 6 months of data using ADD_MONTHS issue

Analytics
Enthusiast

Find last 6 months of data using ADD_MONTHS issue

Hello - I need to find the last 6 months data including the current month and when I use the following code, it returns data back to April 30, 2017. I only want to include months - October, September, August, July, June, May (6 whole months). How do I adjust the code to make sure April is not included?

 

AND calendar_dt >=ADD_MONTHS(CURRENT_DATE,-6)


Accepted Solutions
Junior Contributor

Re: Find last 6 months of data using ADD_MONTHS issue

Use either TRUNC or LAST_DAY:

AND calendar_dt >=TRUNC(ADD_MONTHS(CURRENT_DATE,-5))
AND calendar_dt > LAST_DAY(ADD_MONTHS(CURRENT_DATE,-6))
1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Find last 6 months of data using ADD_MONTHS issue

Use either TRUNC or LAST_DAY:

AND calendar_dt >=TRUNC(ADD_MONTHS(CURRENT_DATE,-5))
AND calendar_dt > LAST_DAY(ADD_MONTHS(CURRENT_DATE,-6))
Enthusiast

Re: Find last 6 months of data using ADD_MONTHS issue

Thank you! Can you explain why I have to add the LAST_DAY() to the ADD_MONTHS()? 

Junior Contributor

Re: Find last 6 months of data using ADD_MONTHS issue

Well, return all data greater than the last day of the month 6 months ago vs. greater than or equal to the first day of the month 5 months ago.