Oracle NVL 函数不允许第二个参数作为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30916914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle NVL function not allows second parameter as datetime
提问by Rashmin Javiya
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.
看起来当我使用字符串或数字而不是trunc(sysdate)它运行良好时。
回答by Rahul Tripathi
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.
NVL 的第一个参数是确定返回列的预期数据类型，trunc 函数将其默认为 NUMBER，因为它的参数为 NULL。NVL 的第二个参数需要匹配它不匹配的数据类型，因为它是一个日期。
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;
回答by Allan
The second parameter must be of the same datatype as the first parameter. The truncfunction 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:
第二个参数必须与第一个参数具有相同的数据类型。该trunc函数已重载。显然，当您传递 a 时null，它会将值解释为一个数字并返回一个数字。以下任何一项工作：
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.
为了获得真正解决您的问题的答案，我建议编辑问题以包含实际演示问题的代码。
