user803860 user803860 - 2 months ago 14
SQL Question

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 xx_new.xx_cti_call_details@appsread.prd.com
where dealer_name = 'XXXX'
and TRUNC(CREATION_DATE) BETWEEN '01-AUG-2012' AND '31-AUG-2012'


Should I use
sysdate-15
function for that?

Answer

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))
Comments