Solved

Oracle 10g Select older than current month

Posted on 2012-03-19
6
461 Views
Last Modified: 2012-03-19
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?
0
Comment
Question by:JDCam
  • 3
  • 2
6 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37738886
and MY_DATE < TRUNC(SYSDATE,'MM')
0
 

Author Closing Comment

by:JDCam
ID: 37738896
Works great. Thank you.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37738915
then try this

select add_months(trunc(to_date(SYSDATE),'MM'),-1) from dual
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37738921
sorry... i was late...!! and didn't refresh my window...
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37738958
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37739235
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now