Inconsistent datatypes expected NUMBER got DATE

I am trying to run this query in Oracle as I need to extract data having Date falling in the previous month with respect to sysdate. This is what I wrote:

SELECT ID, BALANCE, DATE
FROM MY_TABLE
WHERE TRUNC(DATE, 'mm') = TRUNC(ADD_MONTHS((SYSDATE),-1),'mm');

The query does not work and returns the following message:

inconsistent datatypes: expected NUMBER got DATE.

Can anybody help me in solving this?

Solution

Let's assume that you've done something that is not best practice and your DATE column has the NUMBER data type (or VARCHAR2):

CREATE TABLE my_table ( id NUMBER, balance NUMBER, "DATE" NUMBER );

INSERT INTO my_table ( id, balance, "DATE" ) VALUES ( 1, 100, 20210207012345 );

Then your query:

SELECT ID, BALANCE, "DATE"
FROM MY_TABLE
WHERE TRUNC("DATE", 'mm') = TRUNC(ADD_MONTHS((SYSDATE),-1),'mm');

Outputs:

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Not surprising as the "DATE" column is a NUMBER and not a DATE (or if you used VARCHAR2 then TRUNC will implicitly cast the string to a NUMBER so it can truncate it as if it were a decimal value). The error message tells you what to do ... redefine the "DATE" column so that the data type matches the data being stored in the column, i.e. as a DATE data type.

CREATE TABLE my_table ( id NUMBER, balance NUMBER, "DATE" DATE );

INSERT INTO my_table (
  id, balance, "DATE"
) VALUES (
  1, 100, DATE '2021-02-07' + INTERVAL '01:23:45' HOUR TO SECOND
);

Then your query:

SELECT ID, BALANCE, "DATE"
FROM MY_TABLE
WHERE TRUNC("DATE", 'mm') = TRUNC(ADD_MONTHS((SYSDATE),-1),'mm');

Works, and outputs:

ID | BALANCE | DATE               
-: | ------: | :------------------
 1 |     100 | 2021-02-07 01:23:45

You could also use TRUNC( TO_DATE( "DATE", 'YYYYMMDDHH24MISS' ), 'MM' ) in your query (with whichever format model is appropriate) but don't as this is a quick fix that does not address the underlying issue that you are using an inappropriate data type.

db<>fiddle here