ORA-00932: inconsistent datatypes
ORA-00932: expression (20240101) is of data type NUMBER, which is incompatible with expected data type DATETwo values that must have the same data type (a comparison, CASE branches, COALESCE arguments) have types Oracle will not convert between.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-16
Oracle converts between text and numbers or text and dates on its own in many places, but not between NUMBER and DATE, and not when CASE or COALESCE results disagree. Then it stops with ORA-00932.
Older versions say inconsistent datatypes: expected DATE got NUMBER. 23ai names the exact expression: expression (20240101) is of data type NUMBER, which is incompatible with expected data type DATE. Look at that expression first, then check the column type in user_tab_columns.
Causes and fixes
1Comparing a DATE column with a number like 20240101
WHERE order_date >= 20240101 failed: order_date is DATE (user_tab_columns), and 20240101 is a NUMBER, which Oracle will not turn into a date.
Writing the date as TO_DATE('20240101', 'YYYYMMDD') returned orders 2 and 3 (the ones on or after 2024-01-01).
Setup
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, order_date DATE, amount NUMBER);
INSERT INTO orders VALUES (1, DATE '2023-12-28', 100);
INSERT INTO orders VALUES (2, DATE '2024-01-03', 250);
INSERT INTO orders VALUES (3, DATE '2024-01-15', 400);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT order_id, amount FROM orders WHERE order_date >= 20240101;SELECT order_id, amount FROM orders WHERE order_date >= 20240101
*
ERROR at line 1:
ORA-00932: expression (20240101) is of data type NUMBER, which is incompatible with expected data type DATE
Help: https://docs.oracle.com/error-help/db/ora-00932/COLUMN column_name FORMAT A12
COLUMN data_type FORMAT A10
SELECT column_name, data_type FROM user_tab_columns WHERE table_name = 'ORDERS' ORDER BY column_id;COLUMN_NAME DATA_TYPE
------------ ----------
ORDER_ID NUMBER
ORDER_DATE DATE
AMOUNT NUMBER
3 rows selected.SELECT order_id, amount FROM orders WHERE order_date >= TO_DATE('20240101', 'YYYYMMDD') ORDER BY order_id; ORDER_ID AMOUNT
---------- ----------
2 250
3 400
2 rows selected.2A date stored as NUMBER(8) compared with SYSDATE
Many ERP tables keep dates as numbers like 20240101. slip_ymd was NUMBER with precision 8, so WHERE slip_ymd = TRUNC(SYSDATE) compared a NUMBER with a DATE and failed.
Turning today into the same number form, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')), matched today's slip (slip 2, amount 300).
Setup
CREATE TABLE sales_slip (slip_no NUMBER PRIMARY KEY, slip_ymd NUMBER(8), amount NUMBER);
INSERT INTO sales_slip VALUES (1, 20240101, 100);
INSERT INTO sales_slip VALUES (2, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')), 300);
COMMIT;Table created.
1 row created.
1 row created.
Commit complete.SELECT slip_no, amount FROM sales_slip WHERE slip_ymd = TRUNC(SYSDATE);SELECT slip_no, amount FROM sales_slip WHERE slip_ymd = TRUNC(SYSDATE)
*
ERROR at line 1:
ORA-00932: expression (TRUNC(SYSDATE)) is of data type DATE, which is incompatible with expected data type NUMBER
Help: https://docs.oracle.com/error-help/db/ora-00932/COLUMN column_name FORMAT A12
COLUMN data_type FORMAT A10
SELECT column_name, data_type, data_precision FROM user_tab_columns WHERE table_name = 'SALES_SLIP' ORDER BY column_id;COLUMN_NAME DATA_TYPE DATA_PRECISION
------------ ---------- --------------
SLIP_NO NUMBER
SLIP_YMD NUMBER 8
AMOUNT NUMBER
3 rows selected.SELECT slip_no, amount FROM sales_slip WHERE slip_ymd = TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')); SLIP_NO AMOUNT
---------- ----------
2 300
1 row selected.3CASE returns text in one branch and a number in another
CASE WHEN bonus IS NULL THEN 'N/A' ELSE bonus END failed. The first branch is text, so Oracle expected every branch to be text, and bonus is NUMBER.
ELSE TO_CHAR(bonus) made both branches text: KIM got 500, LEE got N/A.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), bonus NUMBER);
INSERT INTO emp VALUES (1, 'KIM', 500);
INSERT INTO emp VALUES (2, 'LEE', NULL);
COMMIT;Table created.
1 row created.
1 row created.
Commit complete.SELECT name, CASE WHEN bonus IS NULL THEN 'N/A' ELSE bonus END AS bonus_text FROM emp ORDER BY emp_id;SELECT name, CASE WHEN bonus IS NULL THEN 'N/A' ELSE bonus END AS bonus_text FROM emp ORDER BY emp_id
*
ERROR at line 1:
ORA-00932: expression ("EMP"."BONUS") is of data type NUMBER, which is incompatible with expected data type CHAR
Help: https://docs.oracle.com/error-help/db/ora-00932/SELECT name, bonus, TO_CHAR(bonus) AS bonus_char FROM emp ORDER BY emp_id;NAME BONUS BONUS_CHAR
------------------------------ ---------- ----------------------------------------
KIM 500 500
LEE
2 rows selected.SELECT name, CASE WHEN bonus IS NULL THEN 'N/A' ELSE TO_CHAR(bonus) END AS bonus_text FROM emp ORDER BY emp_id;NAME BONUS_TEXT
------------------------------ ----------------------------------------
KIM 500
LEE N/A
2 rows selected.4COALESCE with a number column and a text default
COALESCE(bonus, 'N/A') failed because the first argument bonus is NUMBER and 'N/A' is CHAR. COALESCE needs all arguments to be the same type.
A numeric default works (COALESCE(bonus, 0) returned 500 and 0). To show the text N/A, convert the number first: COALESCE(TO_CHAR(bonus), 'N/A') returned 500 and N/A.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), bonus NUMBER);
INSERT INTO emp VALUES (1, 'KIM', 500);
INSERT INTO emp VALUES (2, 'LEE', NULL);
COMMIT;Table created.
1 row created.
1 row created.
Commit complete.SELECT name, COALESCE(bonus, 'N/A') AS bonus_text FROM emp ORDER BY emp_id;SELECT name, COALESCE(bonus, 'N/A') AS bonus_text FROM emp ORDER BY emp_id
*
ERROR at line 1:
ORA-00932: expression ('N/A') is of data type CHAR, which is incompatible with expected data type NUMBER
Help: https://docs.oracle.com/error-help/db/ora-00932/SELECT name, COALESCE(bonus, 0) AS bonus_num FROM emp ORDER BY emp_id;NAME BONUS_NUM
------------------------------ ----------
KIM 500
LEE 0
2 rows selected.SELECT name, COALESCE(TO_CHAR(bonus), 'N/A') AS bonus_text FROM emp ORDER BY emp_id;NAME BONUS_TEXT
------------------------------ ----------------------------------------
KIM 500
LEE N/A
2 rows selected.FAQ
What does ORA-00932 inconsistent datatypes expected DATE got NUMBER mean?
A DATE was required, but a NUMBER was given (for example order_date >= 20240101). Oracle does not convert numbers to dates; use TO_DATE.
How do I compare a NUMBER date column (YYYYMMDD) with SYSDATE?
Convert today's date to the same form: slip_ymd = TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')).
Why does CASE give ORA-00932?
All CASE results must have the same type. If one branch returns 'N/A', wrap number branches in TO_CHAR.
COALESCE(number_column, 'N/A') gives ORA-00932. How do I fix it?
Use COALESCE(TO_CHAR(number_column), 'N/A'), or keep a numeric default such as COALESCE(number_column, 0).
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free