2

We have an Oracle database that we access through OpenQuery for some stuff. You apparently can't do date comparisons directly in OpenQuery using the OleDB driver, so to get around this you have to convert the dates to Julien dates and compare those numbers. I have the following query that we're trying to execute this in MS SQL Server (GPROD is a Linked Server via the OleDb driver):

SELECT *
FROM  OPENQUERY(GPROD, '
     SELECT * 
     FROM ORD_HDR_HST 
     WHERE (cast(to_number(to_char(SHIP_DATE ,''J'')) as numeric(10,0))  >= cast(to_number(to_char(to_date(''01-JAN-2015'') ,''J'')) as numeric(10,0))  AND 
            cast(to_number(to_char(SHIP_DATE ,''J'')) as numeric(10,0))  <= cast(to_number(to_char(to_date(''21-SEP-2015'') ,''J'')) as numeric(10,0)) )')

This query returns no results but also produces no error.

If I execute this query in Oracle SQL Developer, it works just fine and returns thousands of rows:

SELECT * 
FROM ORD_HDR_HST 
WHERE (cast(to_number(to_char(SHIP_DATE ,'J')) as numeric(10,0))  >= cast(to_number(to_char(to_date('01-JAN-2015') ,'J')) as numeric(10,0))  AND 
       cast(to_number(to_char(SHIP_DATE ,'J')) as numeric(10,0))  <= cast(to_number(to_char(to_date('21-SEP-2015') ,'J')) as numeric(10,0)) )

The SHIP_DATE field is of type DATE and is nullable, if that matters.

Does anyone know what I can do to get this working through OpenQuery?

Edit:

I did a test of the Julien Date conversion and there's definitely something fishy going on, but I don't know what's causing it. If I execute this in Oracle:

select cast(to_number(to_char(to_date('01-JAN-2015') ,'J')) as numeric(10,0)) from dual

I get 2457024

If I execute this on SQL Server:

select * from OPENQUERY(GPROD, 'select cast(to_number(to_char(to_date(''01-JAN-2015'') ,''J'')) as numeric(10,0)) from dual')

I get 1721443

Pete
  • 6,585
  • 5
  • 43
  • 69
  • Please fix your tags. This question seens not to be related with sql-server! – Jorge Campos Sep 21 '15 at 15:44
  • It's related to SqlServer and Oracle. – Pete Sep 21 '15 at 15:47
  • Well, I'm sorry. This is actually related with sql-server since oracle DOES NOT have an OPENQUERY function/procedure. Your best bet is to create views on both oracle databases and call it. You also can create database links – Jorge Campos Sep 21 '15 at 15:57
  • This particular query has to run through `OpenQuery`. I can't use views (we do use views for some stuff). Through a view it takes over 40 seconds to execute (table has a lot of rows). Through `OpenQuery`, if it actually worked, it would take a second or so (the time it takes to execute on Oracle directly). – Pete Sep 21 '15 at 16:02
  • Are you trying to execute the query in an oracle database accessing an sqlserver database (remotely) or the other way around? I mean, are connected in an oracle db trying to execute OPENQUERY to sql server or are you connected in a sqlserver trying to execute the openquery to an oracle database? – Jorge Campos Sep 21 '15 at 16:09
  • 1
    Update the question to clarify that the first query is being run in MS SQL Server. – Pete Sep 21 '15 at 16:32
  • Just curious, what happens if you omit the convert to number and just bring the numerical value back as a char? – Brad D Sep 21 '15 at 18:10
  • @BradD I get the same results as above, just as character instead of numeric. That is, "1721443" using `OPENQUERY` and "2457024" in Oracle directly. – Pete Sep 22 '15 at 12:58

1 Answers1

5

I found a solution to the problem. By specifying a mask for the date, it will provide the proper results. Using:

to_char(to_date('01-JAN-2015','DD-MON-YYYY') ,'J')

instead of

to_char(to_date('01-JAN-2015') ,'J')

Gives the same result through OpenQuery and directly from Oracle.

Pete
  • 6,585
  • 5
  • 43
  • 69