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 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?

score 68 · Accepted Answer · edited Sep 11 '12 at 21:16

68

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

edited Sep 11 '12 at 21:16

Spudley

166,037
39
233
307

answered Sep 11 '12 at 21:06

Priti Getkewar Joshi

789
5
4

Yes, trunc('mm') and last_day() is exactly what you want :) – winkbrace Sep 11 '12 at 21:47
2

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. – BoydDensmore Aug 21 '15 at 17:52
i like your code its superb and end of my searching in same topic. – Ashish4434 Sep 23 '16 at 07:24
1

maybe the condition can be optimized: and trunc(creation_date, 'mm') = add_months(trunc(sysdate,'mm'), -1) – Andrei Coșcodan Feb 22 '17 at 16:20
5

@BoydDensmore 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)`. – robinCTS Dec 14 '17 at 09:39

Ben · Answer 2 · 2012-09-11T19:45:42.967

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

Thanks Ben , but above function is giving me data for sep 1st as well. — user803860, Sep 11 '12 at 18:51
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. — Ben, Sep 11 '12 at 18:55
@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. — Ben, Sep 11 '12 at 19:46
But in the qry it is harcoding for sep, i have to run it every month for previous month . — user803860, Sep 11 '12 at 20:13
@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. — Ben, Sep 11 '12 at 20:14

Anjan Biswas · Answer 3 · 2012-09-12T16:50:44.067

3

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');

edited Sep 12 '12 at 16:50

answered Sep 11 '12 at 21:00

Anjan Biswas

7,746
5
47
77

1

I don't think you need TO_CHAR() here ... and might using TRUNC() bypass the index on the column (if there is one)? – David Faber Sep 12 '12 at 15:28
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. – Anjan Biswas Sep 12 '12 at 16:11
You can specify an 'MM' mask in TRUNC() to avoid using TO_CHAR() ... I think using TO_CHAR() would also pull previous years' records. – David Faber Sep 12 '12 at 16:16
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'. – Anjan Biswas Sep 12 '12 at 16:49
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. – David Faber Sep 12 '12 at 18:23

score 2 · Answer 4 · answered Sep 20 '17 at 03:17

2

I believe this would also work:

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  trunc(sysdate, 'mm'))

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

answered Sep 20 '17 at 03:17

kiltannen

1,057
2
12
27

This was a cleaner way to go for me. Much appreciated. – JimT Nov 12 '19 at 16:56

score 2 · Answer 5 · edited Nov 23 '20 at 10:15

2

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

edited Nov 23 '20 at 10:15

Artur Peniche

481
6
27

answered Nov 23 '20 at 09:30

Mohammad Yaqoob

21
2

score 0 · Answer 6 · edited Sep 03 '16 at 06:10

0

Getting last nth months data retrieve

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

edited Sep 03 '16 at 06:10

neer

4,031
6
20
34

answered Sep 03 '16 at 06:05

user6789985

1

Oracle date function for the previous month

6 Answers6

Linked