Oracle 10g Select older than current month

Experts,

I am trying to select records where the date is older than the current month. I am using this:

and MY_DATE <= ADD_MONTHS(TRUNC(SYSDATE), -1)

but it is not working good. Seems to select records based on 30 days, and not the month.
For example on March 19, any record Feburary or older needs to be selected. The above is eliminating records in late Feb.

How do i do this properly?
LVL 1
JDCamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
and MY_DATE < TRUNC(SYSDATE,'MM')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDCamAuthor Commented:
Works great. Thank you.
0
Wasim Akram ShaikCommented:
then try this

select add_months(trunc(to_date(SYSDATE),'MM'),-1) from dual
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wasim Akram ShaikCommented:
sorry... i was late...!! and didn't refresh my window...
0
sdstuberCommented:
wasimibm,

the asker already has the answer, but for your benefit and future readers,  

This has multiple issues:

add_months(trunc(to_date(SYSDATE),'MM'),-1)

first - to_date(sysdate)   -  this doesn't make sense.  SYSDATE is already a date, why would you implicitly convert it to a string and then explicitly convert it back to a date and without a format?  At best it's inefficient, at worst it'll return a corrupted value without error, although it could also return an error and simply fail.

the trunc part is correct assuming you get a valid date out of the first part (not guaranteed though)

the add_months would eliminate all of the previous month as well as the current month.
So, in the asker's example,  March sysdate should return February and older data.  Using add_months on the already truncated value won't work though because that would return January and older data
0
Wasim Akram ShaikCommented:
agreed sdstuber, realised it after it posted the first comment, but was unable to modify it as i had posted some other comment at the same time....

thanks for the clarification..!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.