ORA-00932: inconsistent datatypes

ORA-00932: expression (20240101) is of data type NUMBER, which is incompatible with expected data type DATE

Two 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

  1. Comparing a DATE column with a number like 20240101
  2. A date stored as NUMBER(8) compared with SYSDATE
  3. CASE returns text in one branch and a number in another
  4. COALESCE with a number column and a text default

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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT order_id, amount FROM orders WHERE order_date >= 20240101;
OUTPUT
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/
Checkrun as lab_a
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;
OUTPUT
COLUMN_NAME  DATA_TYPE
------------ ----------
ORDER_ID     NUMBER
ORDER_DATE   DATE
AMOUNT       NUMBER

3 rows selected.
After the fixrun as lab_a
SELECT order_id, amount FROM orders WHERE order_date >= TO_DATE('20240101', 'YYYYMMDD') ORDER BY order_id;
OUTPUT
  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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT slip_no, amount FROM sales_slip WHERE slip_ymd = TRUNC(SYSDATE);
OUTPUT
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/
Checkrun as lab_a
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;
OUTPUT
COLUMN_NAME  DATA_TYPE  DATA_PRECISION
------------ ---------- --------------
SLIP_NO      NUMBER
SLIP_YMD     NUMBER                  8
AMOUNT       NUMBER

3 rows selected.
After the fixrun as lab_a
SELECT slip_no, amount FROM sales_slip WHERE slip_ymd = TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'));
OUTPUT
   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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT name, CASE WHEN bonus IS NULL THEN 'N/A' ELSE bonus END AS bonus_text FROM emp ORDER BY emp_id;
OUTPUT
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/
Checkrun as lab_a
SELECT name, bonus, TO_CHAR(bonus) AS bonus_char FROM emp ORDER BY emp_id;
OUTPUT
NAME                                BONUS BONUS_CHAR
------------------------------ ---------- ----------------------------------------
KIM                                   500 500
LEE

2 rows selected.
After the fixrun as lab_a
SELECT name, CASE WHEN bonus IS NULL THEN 'N/A' ELSE TO_CHAR(bonus) END AS bonus_text FROM emp ORDER BY emp_id;
OUTPUT
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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT name, COALESCE(bonus, 'N/A') AS bonus_text FROM emp ORDER BY emp_id;
OUTPUT
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/
Checkrun as lab_a
SELECT name, COALESCE(bonus, 0) AS bonus_num FROM emp ORDER BY emp_id;
OUTPUT
NAME                            BONUS_NUM
------------------------------ ----------
KIM                                   500
LEE                                     0

2 rows selected.
After the fixrun as lab_a
SELECT name, COALESCE(TO_CHAR(bonus), 'N/A') AS bonus_text FROM emp ORDER BY emp_id;
OUTPUT
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

Related errors