[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ORACLE previous month date function

Posted on 2012-04-02
8
Medium Priority
?
716 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
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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 16

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

608 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