OPENROWSET using ORACLE and DATEDIFF

Hi

Does anybody know the correct syntax to use for DATEDIFF on Oracle when used in OPENROWSET?
I am attempting to return records within a two hour timespan so would expect DATEDIFF('h',date1,date2) to work. However, I receive the following error message when attempting either
WHERE DATEDIFF(''H'',TO_DATE(AA_TIME_STAMP,''yyyy/mm/dd:hh:mi:ss'') , TO_DATE(''2002/10/13:11:00:00'',''yyyy/mm/dd:hh:mi:ss''))<2

or
WHERE DATEDIFF(H,TO_DATE(AA_TIME_STAMP,''yyyy/mm/dd:hh:mi:ss'') , TO_DATE(''2002/10/13:11:00:00'',''yyyy/mm/dd:hh:mi:ss''))<2 or
WHERE DATEDIFF("H",TO_DATE(AA_TIME_STAMP,''yyyy/mm/dd:hh:mi:ss'') , TO_DATE(''2002/10/13:11:00:00'',''yyyy/mm/dd:hh:mi:ss''))<2

(error message:
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00936: missing expression )

Is there a better way?!

What I am attempting to do is create a SQL stored procedure that makes use of two tables within an Oracle database. (EVT_AGENT_ACTIVITY 500,000 rows and CFG_STAFF 300 rows)

My first attempt involved adding the Oracle database as a linked server and joining the tables / WHERE clause on the SQL Server side (we only have read permissions on the Oracle database so cannot create a view). Result:- 525 rows taking over 4 minutes to return.

To try and improve performance, I am attempting to limit the number of rows returned from the Oracle database by

SELECT *
FROM
 OPENROWSET ('MSDAORA', 'cosmos'; 'cosmos'; 'cosmos',   'SELECT AA_STAFF, AA_STAFF_VERSION, AA_TYPE, AA_TIME_STAMP   FROM IGS.EVT_AGENT_ACTIVITY
  WHERE DATEDIFF(''H'',TO_DATE(AA_TIME_STAMP,''yyyy/mm/dd:hh:mi:ss'') , TO_DATE(''2002/10/13:11:00:00'',''yyyy/mm/dd:hh:mi:ss''))<2   ')

Thanks in advance!

Andy Received on Mon Oct 14 2002 - 10:22:07 CDT