title: Using OPENQUERY with an Oracle database to query a date range
body: <p>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):</p>
  
  <pre><code>SELECT *
  FROM  OPENQUERY(GPROD, '
       SELECT * 
       FROM ORD_HDR_HST 
       WHERE (cast(to_number(to_char(SHIP_DATE ,''J'')) as numeric(10,0))  &gt;= 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))  &lt;= cast(to_number(to_char(to_date(''21-SEP-2015'') ,''J'')) as numeric(10,0)) )')
  </code></pre>
  
  <p>This query returns no results but also produces no error.</p>
  
  <p>If I execute this query in Oracle SQL Developer, it works just fine and returns thousands of rows:</p>
  
  <pre><code>SELECT * 
  FROM ORD_HDR_HST 
  WHERE (cast(to_number(to_char(SHIP_DATE ,'J')) as numeric(10,0))  &gt;= 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))  &lt;= cast(to_number(to_char(to_date('21-SEP-2015') ,'J')) as numeric(10,0)) )
  </code></pre>
  
  <p>The <code>SHIP_DATE</code> field is of type DATE and is nullable, if that matters.</p>
  
  <p>Does anyone know what I can do to get this working through OpenQuery? </p>
  
  <p><strong>Edit:</strong></p>
  
  <p>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:</p>
  
  <pre><code>select cast(to_number(to_char(to_date('01-JAN-2015') ,'J')) as numeric(10,0)) from dual
  </code></pre>
  
  <p>I get 2457024</p>
  
  <p>If I execute this on SQL Server:</p>
  
  <pre><code>select * from OPENQUERY(GPROD, 'select cast(to_number(to_char(to_date(''01-JAN-2015'') ,''J'')) as numeric(10,0)) from dual')
  </code></pre>
  
  <p>I get 1721443</p>