"ORA-00936: missing expression" Linked query to Oracle using OpenQuery

Question

I am trying to execute the following query:

SELECT *
FROM OPENQUERY
(
    CLP,
    '
        SELECT *
        FROM ORACLE_TABLE
        WHERE [UPDATEDATE] > ''1900-01-01 12:00 AM''
    '
)

This query works fine when I remove the date criteria. But as soon as I try to pass this criteria it no longer works. I can't figure out what I am missing.

Answer 1

Try to remove [and] and add convert date:

SELECT * 
FROM OPENQUERY
(CLP, 
      '
         SELECT * 
         FROM ORACLE_TABLE 
         WHERE 
         UPDATEDATE > to_date(''1900-01-01 12:00'',''yyyy-mm-dd hh:mi'')
       '
)

or with am

SELECT * 
FROM OPENQUERY
(CLP, 
      '
         SELECT * 
         FROM ORACLE_TABLE 
         WHERE 
         UPDATEDATE > to_date(''1900-01-01 12:00 AM'',''yyyy-mm-dd hh:miam '')
       '
)