Oracle NVL 函数不允许第二个参数作为日期时间

Question

select nvl(trunc(null),trunc(sysdate)) from dual;

在执行上述查询时，我收到以下错误

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

看起来当我采用字符串或数字而不是 trunc(sysdate) 时它运行良好。

Answer 1

来自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

或者你可以这样做:

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