Oracle date function for the previous month

问题:

I have the query below where the date is hard-coded. My objective is to remove the harcoded date; the query should pull the data for the previous month when it runs.

select count(distinct switch_id)
  from [e-mail]
 where dealer_name =  'XXXX'
   and TRUNC(CREATION_DATE) BETWEEN '01-AUG-2012' AND '31-AUG-2012'

Should I use sysdate-15 function for that?

回答1:

Modifying Ben's query little bit,

 select count(distinct switch_id)   
  from [e-mail]  
 where dealer_name =  'XXXX'    
   and creation_date between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1))

回答2:

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 [e-mail]  
 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

回答3:

Data for last month-

select count(distinct switch_id)
  from [e-mail]
 where dealer_name =  'XXXX'
   and to_char(CREATION_DATE,'MMYYYY') = to_char(add_months(trunc(sysdate),-1),'MMYYYY');

回答4:

I believe this would also work:

select count(distinct switch_id)   
  from [e-mail]  
 where 
   dealer_name =  'XXXX'    
   and (creation_date BETWEEN add_months(trunc(sysdate,'mm'),-1) and  trunc(sysdate, 'mm'))

It has the advantage of using BETWEEN which is the way the OP used his date selection criteria.

回答5:

Getting last nth months data retrieve

SELECT * FROM TABLE_NAME 
WHERE DATE_COLUMN BETWEEN '&STARTDATE' AND '&ENDDATE';