Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORACLE previous month date function

Posted on 2012-04-02
8
Medium Priority
?
701 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
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.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.
Suggested Courses

876 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