Oracle NVL function not allows second parameter as datetime

Question

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 1

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;