question

siera_gld avatar image
siera_gld asked

Use Dates in oracle linked server

I have an Oracle Linked server and i need to pull transactions within a given date range but I amd not familiar with how to convert or where to convert the types so the oracle will recognize my criteria SELECT TOP 10 * FROM ORACLE_LS..SCHMA.TABLE_NAME where convert(char(23),INVOICE_DATE,126) between convert(char(23),'2011-11-01',126) and convert(char(23),'2011-11-30', 126)
oracledatesconversionms sql
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered

Try this. I prefer using OPENQUERY and pass through the actual statement, so you do not get messed up with date time formats

SELECT * FROM OPENQUERY(ORACLE_LS,
'SELECT TOP 10 * FROM SCHMA.TABLE_NAME
where INVOICE_DATE between TO_DATE(''2011-11-01'', ''YYYY-MM-DD'')
and TO_DATE(''2011-11-30'', ''YYYY-MM-DD''');

You can also use TO_DATE('01-NOV-2011') which is the default format.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siera_gld avatar image siera_gld commented ·
That TO_DATE function is an Oracle specific syntax and is not resolved by the MS compiler (SSMS)
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Sorry, In haste, I missed to mention the OPENQUERY part. See my edited response. Thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.