locked
Issue datetime oracle linked in SQL Server 2008 RRS feed

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

    Thursday, November 21, 2013 1:37 PM

All replies

  • 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'  

    Tuesday, November 19, 2013 10:42 AM
  • 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

    Wednesday, 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



    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.

    Thursday, November 21, 2013 1:37 PM