Oracle date function for the previous month

Question

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 [email protected]
 where dealer_name =  'XXXX'
   and TRUNC(CREATION_DATE) BETWEEN '01-AUG-2012' AND '31-AUG-2012'

Should I use sysdate-15 function for that?

Pythagoreanism

Answer 1

Modifying Ben's query little bit,

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

Psoas

Yes, trunc('mm') and last_day() is exactly what you want :) – Feliciafeliciano

Using last_day() and trunc() will give you the beginning of the last day though, is that what you want? For me, it gave me last_day(add_months(trunc(sysdate,'mm'),-1)) gave me July 31 2015, 00:00:00. If I use that with between, I'm missing out on all data from July 31st. – Shelah

i like your code its superb and end of my searching in same topic. – Carpal

maybe the condition can be optimized: and trunc(creation_date, 'mm') = add_months(trunc(sysdate,'mm'), -1) – Olympe

@Shelah Unfortunately, the answer does miss out all the data from the last day of the month. However, it is a very simple matter to fix by using the plus operator to add 1 in order to get the beginning of the first day of the next month, like so: (last_day(add_months(trunc(sysdate,'mm'),-1))+1). – Gentlemanatarms

Answer 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 [email protected]  
 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

Engram

Thanks Ben , but above function is giving me data for sep 1st as well. – Pythagoreanism

It's not :-), select * from dual where trunc(sysdate,'mm') > to_date('20120901 00:00:00','yyyymmdd hh24:mi:ss'). The date given is such that it will only allow anything before the first of September. – Engram

I see 18 recs of 9/1/2012. Thanks – Pythagoreanism

@user803860, I've update the query, which should get rid of the problem you're having. It explicitly states that the created date should be less than 2012-09-01 00:00:00. – Engram

But in the qry it is harcoding for sep, i have to run it every month for previous month . – Pythagoreanism

@user803860, there is no hard-coding; the top-query will in September, return everything for August and in October return everything for September. I don't hard-code a date at all. – Engram

Answer 3

Data for last month-

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

Carol

I don't think you need TO_CHAR() here ... and might using TRUNC() bypass the index on the column (if there is one)? – Raoul

I do need to_char to get the Month MM and trunc is to discard any timestamp component. Also, use of any function disables the use of indexes i.e. to_char, trunc, to_date, but if performance is a concern then a Function Based index can be created. I the case above, i may skip trunc anyways since I already use to_char but it doesn't hurt. – Carol

You can specify an 'MM' mask in TRUNC() to avoid using TO_CHAR() ... I think using TO_CHAR() would also pull previous years' records. – Raoul

well, using trunc(date,'MM') will return you the first day of the month and not just the month i.e. it will return 01-SEP-2012 and not SEP or 09, and then you have to do a between in the where clause and so on. The way I said is just a quick and easy way of doing it and very understandable too. And to the year point, I have edited my answer to include 'YYYY'. – Carol

Yes, TRUNC(date, 'MM') will return the first day of the month - so will ADD_MONTHS(TRUNC(sysdate, 'MM'), -1) so one can check for equality. – Raoul

Answer 4

I believe this would also work:

select count(distinct switch_id)   
  from [email protected]  
 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.

Viniculture

This was a cleaner way to go for me. Much appreciated. – Mascagni

Answer 5

It is working with me in Oracle sql developer

    SELECT add_months(trunc(sysdate,'mm'), -1),
           last_day(add_months(trunc(sysdate,'mm'), -1)) 
    FROM dual

Opaline

Answer 6

Getting last nth months data retrieve

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

Luannaluanne