CODEWITHSUNDEEP
sql-serveroracle-databaselinked-serveropenquery
spinon
spinon

Reputation: 10847

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

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.

Upvotes: 1

Views: 3351

Answers (2)

Robert
Robert

Reputation: 25753

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

Upvotes: 1

Anjan Biswas
Anjan Biswas

Reputation: 7912

Use

SELECT *
FROM OPENQUERY
(CLP,SELECT * FROM ORACLE_TABLE WHERE trunc(UPDATEDATE) > ''01-JAN-1900'')

All dates with no time component on them defaults to 12:00 AM (or 00:00 Hrs) in Oracle.

You can also use to_timestamp(UPDATEDATE) but for this to work the column should be of timestamp type (i.e. contain timestamp on it otherwise it would always give 12 AM). You can also use to_char(UPDATEDATE,'YYYY-MM-DD HH:MI AM').

Upvotes: 0

Related Questions