CODEWITHSUNDEEP
sqloracle-database
Oracle rookie
Oracle rookie

Reputation: 95

retrieving last two years data from current sysdate

Hi i am stuck on an oracle query wherein i want to retrieve data from previous two years based on the current year like this is 2013 so i want to retrieve all records from year 2011 and 2012 i have done the following query but m getting stuck on date formats i am pretty new to oracle here is the query

Select 
to_date(stage_end_date,'yy') Years,SUBPRODUCT Product,sum(OFFER_COUNT) SumCount
    ,sum(offer_amount)SumAmount
from stage_amt  
and 
Offer_amount !=0 
and 
to_date(stage_end_date,'yy')
between to_char( sysdate, -2 ) and to_char( sysdate)
group by to_date(stage_end_date,'yy'),SUBPRODUCT
order by Years asc;

m getting this error ORA-00932: inconsistent datatypes: expected DATE got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s" can any one help me what to do

regards

Upvotes: 2

Views: 23898

Answers (2)

pratik garg
pratik garg

Reputation: 3342

with sysdate you are appling to_char function but giving incorrect date formate -

I believe that stage_end_date is a date column, if so, I think you need to use to_char function in place of to_date in select query ... try below query -

Select To_Char(stage_end_date,'yy') Years,
       SUBPRODUCT Product,
       sum(OFFER_COUNT) SumCount,
       sum(offer_amount)SumAmount
from stage_amt  
where Offer_amount !=0 
  and to_number(To_char(stage_end_date,'YYYY')) between 
      to_number(to_char( sysdate,'YYYY'))-2 and to_number(to_char(sysdate,'YYYY'))
group by to_char(stage_end_date,'yy'),
         SUBPRODUCT
order by Years asc;

Although Oracle will take care or data conversion from string to number while comparing but for safer side I have wrote TO_NUMBER function also while comparing dates.

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

How about this?

where stage_end_date >= trunc(sysdate, 'yyyy') - interval '2' year
  and stage_end_date <  trunc(sysdate, 'yyyy');

Upvotes: 8

Related Questions