Using OPENQUERY with an Oracle database to query a date range

Question

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

Please fix your tags. This question seens not to be related with sql-server! — Jorge Campos
– Jorge Campos, Commented Sep 21, 2015 at 15:44
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
– Jorge Campos, Commented Sep 21, 2015 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
– Pete, Commented Sep 21, 2015 at 16:02
Update the question to clarify that the first query is being run in MS SQL Server. — Pete
– Pete, Commented Sep 21, 2015 at 16:32

Pete · Accepted Answer · 2015-09-22 14:07:22Z

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.

answered Sep 22, 2015 at 14:07

Pete

6,7716 gold badges48 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using OPENQUERY with an Oracle database to query a date range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related