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

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

Answer

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

---

[EDIT]

This is what you were supposed to provide; now I had to do it, based on your comment:

DRCR_CR_DT is a varchar2 the containt of DRCR_CR_DT is 01/04/2022 00:00:00

Therefore:

SQL> create table ps_drcr (drcr_cr_dt varchar2(20));

Table created.

SQL> insert into ps_drcr values ('01/04/2022 00:00:00');


1 row created.

Query: TO_DATE‘s format model must match data format. If table contains 00:00:00, then format model is hh24:mi:ss, not hh:mi:ss am you used.

In that case, query works for this particular value.

SQL> select *
  2  from ps_drcr
  3  where TRUNC(TO_DATE(DRCR_CR_DT, 'DD/MM/YYYY HH24:MI:SS')) =
  4        TO_DATE('1/4/2022','dd/mm/yyyy');

DRCR_CR_DT
--------------------
01/04/2022 00:00:00

SQL>

Note that it is always a really bad idea to store date datatype values as strings (into varchar2 columns) because nothing prevents you from storing e.g. ab/&4/20xz 0a:f8:fi into a varchar2 column, and that certainly isn’t valid date value. If there is at least one row in the table whose drcr_cr_dt doesn’t represent valid date, query will fail.