oracle-databaseora-00932

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 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.

Solution

  • 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))