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;

Answer 2

The second parameter must be of the same datatype as the first parameter. The trunc function is overloaded. Apparently when you pass it a null, it interprets the value as a number and returns a number. Any of the following works:

SELECT NVL (NULL, TRUNC (SYSDATE)) FROM DUAL;

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

SELECT NVL (TRUNC (CAST (NULL AS DATE)), TRUNC (SYSDATE)) FROM DUAL;

---

Follow up:

If the variable that is being used is properly defined as a date, then you should not have a problem. As an example, the following runs without error:

DECLARE
   FUNCTION f
      RETURN DATE IS
   BEGIN
      RETURN NULL;
   END f;
BEGIN
   DBMS_OUTPUT.put_line (NVL (TRUNC (f), TRUNC (SYSDATE)));
END;

In order to get an answer that actually solves your problem, I would recommend editing the question to include code that actually demonstrates the problem.