add_months function in oracle

mrong
mrong used Ask the Experts™
on
Greeting,

I try to use add_months function to get me all the data for the previous 6 month up to now.
I have select * from tbl where date>=add_months(sysdate,-6)
The above sql will give me the data from Nov 13th to May 13th(Now).
How to get data from Nov 1st to May 13th?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select * from tbl where date>= trunc(add_months(sysdate,-6),'mm')
Most Valuable Expert 2011
Top Expert 2012

Commented:
you could put the trunc on sysdate as well

select * from tbl where date>=add_months(trunc(sysdate,'mm'),-6)


both of these assume you want everything from Nov 1 of previous year up through current day and time, and that there is no future-dated data

if you might have future-dated data, then add an additional clause

AND date <= sysdate
You can also use this:

SELECT * FROM <<TABLE>> WHERE trunc(<<YOUR DATE COLUMN>>,'MM') >= ADD_MONTHS(SYSDATE,-6)
AND <<YOUR DATE COLUMN>> <= sysdate;

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
putting TRUNC on the column prevents use of most  indexes and isn't recommended

Commented:
select * from tbl where date>='01-'||to_char(add_months(sysdate,-6),'MON-YYYY')

OR

select * from tbl where date>=LAST_DAY(add_months(sysdate,-7))+1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial