Unhelpful Oracle error message: expected %s, got %s using to_date

oracle ora-00932
ale picture ale · Aug 29, 2011 · Viewed 13.1k times · Source

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.

Answer

Gerrat picture Gerrat · Aug 29, 2011

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.

Related questions

Oracle 10g: Extract data (select) from XML (CLOB Type)

I'm new in Oracle and I've - maybe trivial - a problem in a select. (I'm using Oracle 10g Express Edition). I've a DB with a field CLOB: mytab.xml This column have an XML like this: <?xml version="1.0" …

Read More
What is the best way to search the Long datatype within an Oracle database?

I am working with an Oracle database that stores HTML as a Long datatype. I would like to query the database to search for a specific string within the HTML data stored in the Long. I tried, "select * from TABLE …

Read More
SELECT DISTINCT CLOB_COLUMN FROM TABLE;

I would like to find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB) contained in the table called COPIA. I have selected a PROCEDURAL WAY to solve this problem, but I would prefer …

Read More