ORA-00932: inconsistent datatypes: expected NUMBER got DATE in oracle stored procedure

Question

I have below query which i am using in oracle stored procedure where i am getting error as

ORA-00932: inconsistent datatypes: expected NUMBER got DATE.

day_id column is Number datatype and the data is stored in this column like for e.g 20110822 and ltds column is date datatype and value is stored in this column for e.g 08-AUG-17. Both the column stored the actual date.

In my stored procedure i am putting this query in variable like below:

NESS_QUERY:= select t.day_id from M_TIME t 
where TO_DATE (t.day_id, ''YYYYMMDD'') < trunc('''||LTDS||''') and 
TO_DATE (t.day_id, ''YYYYMMDD'') >= trunc(sysdate, ''mm'') - case trunc('''||LTDS||''') when trunc(sysdate, ''mm'') then 1 else 0 end;

DBMS_OUTPUT.PUT_LINE (NESS_QUERY);

The output of DBMS_OUTPUT.PUT_LINE is below:

select t.day_id from M_TIME t 
where TO_DATE (t.day_id, 'YYYYMMDD') < trunc('15-DEC-17') and 
TO_DATE (t.day_id, 'YYYYMMDD') >= trunc(sysdate, 'mm') - case trunc('15-DEC-17') when trunc(sysdate, 'mm') then 1 else 0 end

Answer 1

If you have any influence over the choice of datatype for this column, please get it changed to be of DATE datatype. That will make things (like the query you're attempting to run) much easier.

As your column is not currently DATE datatype, if you want to compare the contents of the column to a date, you need to convert the column via to_date() along with an appropriate format mask. In your case, it looks like the format mask is yyyymmdd.

So your query should be something along the lines of:

select t.day_id
from   m_time t
where  to_date(t.day_id, 'yyyymmdd') >= trunc(sysdate, 'mm') - case trunc(ltds) when trunc(sysdate, 'mm') then 1 else 0 end;