oracle ORA-00932: 不一致的数据类型：预期的 INTERVAL DAY TO SECOND 得到 CHAR
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26254196/
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
ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got CHAR
提问by Thush
SELECT COALESCE (
(to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS')
- ('2014-09-22 09:00:00' ,'yyyy/mm/dd HH24:MI:SS'))
- (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS')
- to_timestamp('2014-09-22 09:00:00.' ,'yyyy/mm/dd HH24:MI:SS'))
, '00:00')
FROM DUAL;
This is working in postgres but it is not working in oracle.
这在 postgres 中有效，但在 oracle 中无效。
回答by ravi chaudhary
It looks like you are trying to do maths (+, -) with TIMESTAMP. TIMESTAMP doesn't like that. you should CAST the TIMESTAMP to DATE:
看起来您正在尝试使用 TIMESTAMP 进行数学运算 (+, -)。TIMESTAMP 不喜欢那样。您应该将时间戳转换为日期：
rather than bla - blu (where bla and blu are TIMESTAMP)
而不是 bla - blu（其中 bla 和 blu 是时间戳）
do
CAST (bla as DATE) - CAST (blu as DATE)
and you will get a NUMBER (multiply it by 3600 * 24 and you will turn it into seconds)
BUT you will lose the millisecond info
但是你会丢失毫秒信息
check this link timestamp difference
检查此链接时间戳差异
Here you have the definition of the TIMESTAMP
回答by Rimas
Change '00:00'to INTERVAL '0' DAY:
更改'00:00'为INTERVAL '0' DAY：
SELECT COALESCE (
(to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')) -
(to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')),
INTERVAL '0' DAY)
FROM DUAL;
More info: Interval Literals
更多信息：区间文字
回答by Lalit Kumar B
- With only one expressions to check,
COALESCEis not required.NVLwould suffice. Basically, if there is no time difference, it would return 0. DATEhas a time part, theTIMESTAMPdatatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. So, in your case,TO_DATEmakes more sense.
- 只检查一个表达式，
COALESCE不是必需的。NVL就足够了。基本上，如果没有时差，它会返回0。 DATE有时间部分，TIMESTAMP数据类型是 DATE 数据类型的扩展。除了 DATE 数据类型的 datetime 元素之外，TIMESTAMP 数据类型将秒的分数保存到 0 到 9 个小数位之间的精度，默认值为 6。因此，在您的情况下，TO_DATE更有意义。
SQL> SELECT NVL( 2 (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 3 - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 4 ,0) DIFF 5 FROM DUAL 6 / DIFF ---------- 0 SQL>
SQL> SELECT NVL( 2 (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 3 - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS')) 4 ,0) DIFF 5 FROM DUAL 6 / DIFF ---------- 0 SQL>
For other values, to get a significant difference of time interval :
对于其他值，要获得显着的时间间隔差异：
SQL> SELECT NVL(
2 (to_date( '2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 23:00:00','YYYY-MM-DD HH24:MI:SS'))
3 - (to_date('2014-09-22 16:00:00','YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-22 09:00:00','YYYY-MM-DD HH24:MI:SS'))
4 ,0) DIFF
5 FROM DUAL
6 /
DIFF
----------
-.58333333
Update
更新
Since the difference of the dates returns a number, using NVLwith TO_DATEwon't return intervalbut a number. As, in above example, it is 0.
由于日期的差异返回一个数字，因此使用NVLwithTO_DATE不会返回interval而是 a number。如上例所示，它是 0。
To get the interval in the difference, to_timestampmakes sense. So, NVL and TO_TIMESTAMP would be good :
要获得差异的间隔，to_timestamp是有道理的。所以，NVL 和 TO_TIMESTAMP 会很好：
SQL> SELECT NVL (
2 (to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')) -
3 (to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')),
4 INTERVAL '0' DAY) diff
5 FROM DUAL
6 /
DIFF
---------------------------------------------------------------------------
+000000000 00:00:00.000000000
