Problem with linked server Sql query

[swenri]

I created a linked server using Microsoft OLE DB Provider for Oracle. I created an inner join query in Sql Server Management Studio.

The inner join query works without the date selection parameter but, when I use the date selection parameter
it throws an error Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '01'.


AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN to_date('01-JAN-2009', 'dd-MON-YYYY') AND to_date('31-JAN-2009','dd-MON-YYYY' ' ) AS a


I’ve tried every possible way to solve it by changing the date format but, nothing seems to work.



SELECT a.* FROM OPENQUERY(TESTORALINK,'SELECT MASTER50_ENT.ORDERS.ORDER_ID, _

MASTER50_ENT.ACCOUNT.EXTERNAL_ACCOUNT_NUMBER, _

MASTER50_ENT.CONTACT.COMPANY, _

MASTER50_ENT.ACCOUNT.COUNTRY_CD

FROM MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT

WHERE MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID

AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID

AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN to_date('01-JAN-2009', 'dd-MON-YYYY') AND to_date('31-JAN-2009','ddMONYYYY' ' ) AS a



Also, another question is how create a report in SSRS 2005 using the same linked server ?


Thank you for the help ….