Answered by:
Issue datetime oracle linked in SQL Server 2008

Question
-
Hi guys, one question: I got a linked oracle db that has a field as datetime2 , 7
I tried with cast and convert to set a new field just to create a condition where but it doesn't work. So far:
with cte as (select top 5 bc.amount, convert(date,b.UPDATE_DATETIME) AS sqldate) select * from cte where sqldate='01/10/2013'
Operand type clash: date is incompatible with int
That's one of the errors I ran through.
Any sugegstion?
Tuesday, November 19, 2013 10:13 AM
Answers
-
Defineteley I resolved the issue now and forever:
where [PERIOD_START_DATE] between ('01-oct-2013') and ('31-oct-2013')
It's works perfectly. You need just to write the date in the format: dd-month (first three letters)-yyyy
Just in case anyone else has my same problem.
- Marked as answer by Allen Li - MSFT Sunday, December 1, 2013 10:52 AM
Thursday, November 21, 2013 1:37 PM
All replies
-
-
Hi DIEGOCTN,
I have little knowledge about Oracle commands, with linked server, the commands passed to Oracle server are Oracle commands rather than Transact-SQL commands. For example, in the following command, the select query is a Oracle command.
SELECT * FROM OPENQUERY(OraLink,'SELECT ColA, ColB, CASE WHEN ColC < TO_DATE(''17530101'',''YYYYMMDD'') THEN TO_DATE(''99991231'',''YYYYMMDD'') ELSE ColC END, ColD FROM OraSchema.OraTable');
Since our current concern is about how to generate this Oracle command, I think it is better to post the question to Oracle forum. Additionally, thanks for sharing your workaround. I listed a related document, hope it helps.
Linked server vs. OPENQUERY for handling data type conversions
http://sqlblog.com/blogs/john_paul_cook/archive/2009/07/31/linked-server-vs-openquery-for-handling-data-type-conversions.aspxBest Regards,
Allen LiAllen Li
TechNet Community SupportWednesday, November 20, 2013 7:34 AM -
could you try this?
with cte as (select top 5 bc.amount, convert(datetime,b.UPDATE_DATETIME) AS sqldate) select * from cte where cast(sqldate as date)='01/10/2013'
Satheesh
My Blog
- Edited by Satheesh Variath Wednesday, November 20, 2013 8:46 AM ******
Wednesday, November 20, 2013 8:46 AM -
Defineteley I resolved the issue now and forever:
where [PERIOD_START_DATE] between ('01-oct-2013') and ('31-oct-2013')
It's works perfectly. You need just to write the date in the format: dd-month (first three letters)-yyyy
Just in case anyone else has my same problem.
- Marked as answer by Allen Li - MSFT Sunday, December 1, 2013 10:52 AM
Thursday, November 21, 2013 1:37 PM