Oracle date function for the previous month
Modifying Ben's query little bit,
select count(distinct switch_id) from xx_new.xx_cti_call_details@appsread.prd.com where dealer_name = 'XXXX' and creation_date between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1))
The trunc()
function truncates a date to the specified time period; so trunc(sysdate,'mm')
would return the beginning of the current month. You can then use the add_months()
function to get the beginning of the previous month, something like this:
select count(distinct switch_id) from xx_new.xx_cti_call_details@appsread.prd.com where dealer_name = 'XXXX' and creation_date >= add_months(trunc(sysdate,'mm'),-1) and creation_date < trunc(sysdate, 'mm')
As a little side not you're not explicitly converting to a date in your original query. Always do this, either using a date literal, e.g. DATE 2012-08-31
, or the to_date()
function, for example to_date('2012-08-31','YYYY-MM-DD')
. If you don't then you are bound to get this wrong at some point.
You would not use sysdate - 15
as this would provide the date 15 days before the current date, which does not seem to be what you are after. It would also include a time component as you are not using trunc()
.
Just as a little demonstration of what trunc(<date>,'mm')
does:
select sysdate , case when trunc(sysdate,'mm') > to_date('20120901 00:00:00','yyyymmdd hh24:mi:ss') then 1 end as gt , case when trunc(sysdate,'mm') < to_date('20120901 00:00:00','yyyymmdd hh24:mi:ss') then 1 end as lt , case when trunc(sysdate,'mm') = to_date('20120901 00:00:00','yyyymmdd hh24:mi:ss') then 1 end as eq from dual ;SYSDATE GT LT EQ----------------- ---------- ---------- ----------20120911 19:58:51 1
Data for last month-
select count(distinct switch_id) from xx_new.xx_cti_call_details@appsread.prd.com where dealer_name = 'XXXX' and to_char(CREATION_DATE,'MMYYYY') = to_char(add_months(trunc(sysdate),-1),'MMYYYY');