无用的 Oracle 错误消息：预期为 %s，使用 to_date 获得 %s
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7233009/
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
Unhelpful Oracle error message: expected %s, got %s using to_date
提问by ale
Here is a simple query:
这是一个简单的查询：
SELECT COUNT(*) FROM m_bug_t
WHERE date_submitted BETWEEN TO_DATE('2011-08-22','yyyy-mm-dd') AND TO_DATE('2011-08-29','yyyy-mm-dd')
AND status != 100
that gives the following error message
这给出了以下错误消息
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 2 Column: 22
Any ideas? I'm using to using MySQL where this works even without the to_date function.
有任何想法吗？我习惯于使用 MySQL，即使没有 to_date 函数也能正常工作。
回答by Gerrat
It looks like the date_submittedcolumn is numeric, and you're trying to compare it to a date. Oracle won't let you do this.
该date_submitted列看起来像是数字，而您正试图将其与日期进行比较。Oracle 不会让你这样做。
[EDIT:] Assuming that the Epochis Jan 1, 1970, you should be able to use:
[编辑:] 假设纪元是 1970 年 1 月 1 日，您应该能够使用：
TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS') + (date_submitted / (24 * 60 * 60))
To get the actual date that is represented. I'm not sure if this will be 100% accurate, since your date in seconds may not include leap seconds and Oracle's likely does.
获取表示的实际日期。我不确定这是否是 100% 准确的，因为您以秒为单位的日期可能不包括闰秒，而 Oracle 可能会。
回答by Thomas Jones-Low
Converting Oracle dates to unix timestamp values requires the following function:
将 Oracle 日期转换为 unix 时间戳值需要以下函数：
SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) as dt FROM dual;
or in the case of your sql where clause:
或者在你的 sql where 子句的情况下：
WHERE date_submitted between
((TO_DATE('2011-08-22', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))
AND
((TO_DATE('2011-08-29', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))
