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.
What should I use?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select trunc(trunc(sysdate,'MM')-
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
wdosanjos - looks like you simply copied the first post with select from dual wrapped around it
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.
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.
@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.
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.
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.
Try truncating the day of the month:
TRUNC(<date>,'MM')
HTH,
Philippe