asked on 5/13/2011

add_months function in oracle

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.

Oracle Database

Last Comment

daddyhaw

5/25/2011

ASKER CERTIFIED SOLUTION

Sean Stuber

5/13/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Sean Stuber

5/13/2011

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

Swadhin Ray

5/18/2011

You can also use this:

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

Sean Stuber

5/18/2011

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

Swadhin Ray

5/24/2011

yeah true

daddyhaw

5/25/2011

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