Oracle NVL function not allows second parameter as datetime

Rashmin Javiya picture Rashmin Javiya · Jun 18, 2015 · Viewed 14.7k times · Source
select nvl(trunc(null),trunc(sysdate)) from dual;

While executing above query i am getting following error

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

look like when i take string or number instead of trunc(sysdate) it run fine.

Answer

Rahul Tripathi picture Rahul Tripathi · Jun 18, 2015

From here:

The first parameter to NVL is determining the expected datatype of the returned column, with the trunc function defaulting that to NUMBER because it's parameter is NULL. The second parameter to NVL needs to match that datatype which it doesn't because it is a date.

SQL> select nvl(trunc(sysdate), sysdate) as mydate from dual;

MYDATE
-------------------
26/05/2006 00:00:00

SQL> select nvl(trunc(null), sysdate) as mydate from dual;
select nvl(trunc(null), sysdate) as mydate from dual
                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

or you can do like this:

SELECT NVL(TO_DATE(TRUNC(NULL)),SYSDATE) FROM dual;