Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 10g Select older than current month

Posted on 2012-03-19
6
Medium Priority
?
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 74

Accepted Solution

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

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 16

Expert Comment

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 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