Solved

ORACLE previous month date function

Posted on 2012-04-02
8
643 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 500 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

710 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