Oracle Date in Where Clause give me this error ORA-00932: inconsistent datatypes: expected NUMBER go-CodePudding

sql request is:

select DRCR_CR_DT 
from PS_DRCR 
where 
TRUNC(DRCR_CR_DT)= TO_DATE('1/4/2022','dd/mon/yyyy');

the type of DRCR_CR_DT is: DATE

DRCR_CR_DT contain something like this : 1/4/2022 2:02:54 PM

thank you for the answer

CodePudding user response：

The error means that DRCR_CR_DT is a string, not a real date - so its data type is VARCHAR2 not DATE as you said.

create table PS_DRCR (DRCR_CR_DT) AS
select '1/4/2022 2:02:54 PM' from dual;

select DRCR_CR_DT 
from PS_DRCR 
where 
TRUNC(DRCR_CR_DT)= TO_DATE('1/4/2022','dd/mon/yyyy');

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

If it was really a date it would work - at least, with the format model fixed as @Littlefoot pointed out.

If you are stuck with it as a string (which is a bad data model) then you need to convert it to a date before truncating:

select DRCR_CR_DT 
from PS_DRCR 
where 
TRUNC(TO_DATE(DRCR_CR_DT, 'DD/MM/YYYY HH:MI:SS AM')) = TO_DATE('1/4/2022','dd/mm/yyyy');

DRCR_CR_DT
-------------------
1/4/2022 2:02:54 PM

You can also compare with a date literal:

select DRCR_CR_DT 
from PS_DRCR 
where 
TRUNC(TO_DATE(DRCR_CR_DT, 'DD/MM/YYYY HH:MI:SS AM')) = DATE '2022-04-01';

db<>fiddle

The reason you get ORA-00932 is that before it looks at the actual values involved the parser sees something like:

where TRUNC(<some string>) = TO_DATE(<some string>)

The numeric form of TRUNC "takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type", while the date form only takes a "datetime data type". That means TRUNC(<some string>) has to be interpreted as a number.

So without looking at either string to be converted, it knows that translates to:

where <some number> = <some date>

It's doing that analysis before it does any actual conversions, so it doesn't get as far as trying to convert the date string; when it does get that far that will throw ORA-01843.

CodePudding user response：

I'd expect something different; if date you provide is 1/4/2022, then there's no mon format model in it:

SQL> select to_date('1/4/2022', 'dd/mon/yyyy') from dual;
select to_date('1/4/2022', 'dd/mon/yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

When fixed:

SQL> select to_date('1/4/2022', 'dd/mm/yyyy') from dual;

TO_DATE('1
----------
01/04/2022

SQL>

If that's not it, please, provide sample data (CREATE TABLE and INSERT INTO statements).