Unhelpful Oracle error message: expected %s, got %s using to_date
Here is a simple query:
SELECT COUNT(*) FROM m_bug_t
WHERE date_submitted BETWEEN T开发者_高级运维O_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.
It looks like the date_submitted
column is numeric, and you're trying to compare it to a date. Oracle won't let you do this.
[EDIT:] Assuming that the Epoch is Jan 1, 1970, you should be able to use:
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.
Converting Oracle dates to unix timestamp values requires the following function:
SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) as dt FROM dual;
or in the case of your sql where clause:
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))
精彩评论