CODEWITHSUNDEEP
oracle-databasestored-proceduresdate-conversion
Marcus
Marcus

Reputation: 3869

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

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

Upvotes: 2

Views: 39120

Answers (4)

Jon Albert
Jon Albert

Reputation: 1

where (t.day_id, 'YYYYMMDD') < trunc('31-DEC-23') and 
TO_DATE (t.day_id, 'YYYYMMDD') >= trunc(sysdate, 'mm') - case trunc('01-Jan-23') when trunc(sysdate, 'mm') then ('Yes') else ('No') end

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

You should convert day_id to date before comparison, using TO_DATE

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;

EDIT : You don't need to use the ldts variable in your query, you may save it and use it in other places, but for query you could define it and reuse from cte.

WITH l AS (SELECT TRUNC (LOAD_DATE) LDTS FROM JOB_EXE)
SELECT t.day_id
  FROM M_TIME t CROSS JOIN l
 WHERE     TO_DATE (t.day_id, 'YYYYMMDD') < l.LDTS
       AND TO_DATE (t.day_id, 'YYYYMMDD') >=
                TRUNC (SYSDATE, 'mm')
              - CASE l.LDTS WHEN TRUNC (SYSDATE, 'mm') THEN 1 ELSE 0 END

Upvotes: 1

eifla001
eifla001

Reputation: 1157

below condition causing the error, 

t.day_id >= trunc(sysdate, 'mm')

day_id is a number, trunc(sysdate, 'mm') returns date datatype

Upvotes: 1

Boneist
Boneist

Reputation: 23588

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;

Upvotes: 2

Related Questions