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?

Answer 1

I found out this solution (but I don't l;ike it) :

with cte as

cast (cast (b.update_time as char) as date) as sqldate

select * from cte where sqldate='2009-10-25'  

Answer 2

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.aspx

Best Regards,
Allen Li

---

Allen Li
TechNet Community Support

Answer 3

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

Answer 4

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.