Compare SQL DATE to NUMBER

I am working with the following SQL statement and can't seem to get the syntax correct:

idSelectQuery=Select "numberprgn" 
              from "SMT1"."CM3R1M1" 
              where "category" != 'KM Document' 
          AND ("sysmodtime" - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400000 > ?

Where the '?' is the current system time as a number - which is actually epoch time.

Th开发者_如何学运维is statement fails because of:

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

I need to be able to compare the sysmodtime to the ? time value, although I can't figure out a way to properly do this... I'm not sure if i can cast sysmodtime to char then to number, because I know its not possible to cast date to number directly.

Anyone have any suggestions to get this to work?

Thanks

I suspect you want

"sysmodtime" > TO_DATE('1970-01-01', 'YYYY-MM-DD') + (?/ 86400000)

Assuming that ? represents the number of milliseconds since Jan 1, 1970. Writing the query this way makes it possible to use an index on "sysmodtime".

Now, realistically, you probably want to create a function that takes a numeric time and converts it to a date, i.e.

CREATE FUNCTION epoch_to_date( p_epoch IN NUMBER )
  RETURN DATE
IS
BEGIN
  RETURN TO_DATE('1970-01-01', 'YYYY-MM-DD') + (p_epoch/ 86400000);
END;

that you can call in your query

"sysmodtime" > epoch_to_date( ? )