使用OPENQUERY与Oracle数据库来查询日期范围

问题描述

我们有一个Oracle数据库，我们通过OpenQuery访问某些东西。您显然不能使用OleDB驱动程序直接在OpenQuery中进行日期比较，所以要解决这个问题，您必须将日期转换为Julien日期并比较这些数字。我有以下查询，我们试图在MS SQL Server（GPROD是通过OleDb驱动程序的链接服务器）中执行此操作：

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.

如果我在Oracle SQL Developer中执行此查询，它工作得很好，并返回数千行：

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)) )

SHIP_DATE 字段的类型为DATE，如果重要，则为空。

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

有没有人知道我可以做些什么来让这个工作通过OpenQuery？

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

编辑

我测试了Julien Date转换，绝对有点腥味，但我不知道是什么原因造成的。如果我在Oracle中执行此操作：

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

我得到2457024

I get 2457024

如果我在SQL Server上执行此操作：

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

我得到1721443

I get 1721443

推荐答案

解决问题。通过指定日期的掩码，它将提供正确的结果。使用：

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

而不是

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

通过OpenQuery直接从Oracle获得相同的结果。

Gives the same result through OpenQuery and directly from Oracle.

这篇关于使用OPENQUERY与Oracle数据库来查询日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！