?
Solved

ORACLE previous month date function

Posted on 2012-04-02
8
Medium Priority
?
673 Views
Last Modified: 2012-04-02
I'd like to create a report which will run on the 1st of each month showing data for the previous month.

What should I use?

Thanks.
0
Comment
Question by:saved4use
[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
  • 2
  • +1
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37798000
add_months

add_months(trunc(sysdate,'mm'),-1) first_day_of_previous_month,
 trunc(sysdate,'mm')-1 last_day_of _previous_month

putting them together...


select * from your_table
where your_date >= add_months(trunc(sysdate,'mm'),-1)
and your_date < trunc(sysdate,'mm')


note, I didn't use the last day, that way I would include all values of the last day, up to and including 23:59:59
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 37798004
Hi,
Try truncating the day of the month:

TRUNC(<date>,'MM')

HTH,

Philippe
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 37798009
select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:wdosanjos
ID: 37798027
Here is another option:

select 
  trunc(add_months(sysdate, -1),'MONTH') First_Day_Previous_Month, 
  last_day(add_months(sysdate,-1)) Last_Day_Previous_Month
from dual

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37798097
wdosanjos - looks like you simply copied the first post  with select from dual wrapped around it
0
 
LVL 15

Expert Comment

by:Deepak Chauhan
ID: 37798175
you can use

SELECT * from table name where date >= TO_CHAR(SYSDATE,  -1)    and
 < TO_CHAR(SYSDATE,  -1)  previous_month from tablename or dual;

this will subtract one month from the current date and as according to you will run the report  on 1st of every month.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37798181
@sdstuber: I didn't mean to plagiarize.  I was working on my solution, before I saw the other posts.  Nonetheless, my solution uses the LAST_DAY function, which is not used on the first post.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37798278
you're right, I missed the last_day sorry,

but, check the first post again, I made a note about using the last day and why that might not really be what you want to do
also note, even if your data happens to be truncated to the day so you don't have to worry about time,  the query in the first post would still work.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

719 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