Phunhog (8/20/2012)
Hello Oracle Forum:I've got my first linked server established from SQL Server 2008 SSMS to Oracle.
What is the proper syntax for this simple query with a date expression in the where clause?
What is the proper date delimiter?
Is there a conversion necessary?
SELECT TOP (5) SURGERY_DATE
FROM OPENQUERY(CLARITY, 'SELECT * FROM OR_LOG WHERE SURGERY_DATE = ('8/1/2012')' AS a
First, there is no TOP clause on Oracle - if you want the first five rows returned you have to wrap-up the query like this...
select *
from
(
type-here-your-query-including-order-by-clause
)
where rownum < 6;
Second, assuming SURGERY_DATE is of the DATE data type syntax should go like...
... where SURGERY_DATE = TO_DATE('08/01/2012', 'mm/dd/yyyy')
This is assuming 08 represents the month and 01 represents the day on the month.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.