ORACLE previous month date function

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.
saved4useAsked:
Who is Participating?
 
sdstuberCommented:
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
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi,
Try truncating the day of the month:

TRUNC(<date>,'MM')

HTH,

Philippe
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wdosanjosCommented:
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
 
sdstuberCommented:
wdosanjos - looks like you simply copied the first post  with select from dual wrapped around it
0
 
Deepak ChauhanSQL Server DBACommented:
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
 
wdosanjosCommented:
@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
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.