在 Oracle 数据库中使用 OPENQUERY 查询日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32699299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using OPENQUERY with an Oracle database to query a date range
提问by Pete
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):
我们有一个 Oracle 数据库，我们可以通过 OpenQuery 访问该数据库以获取某些内容。您显然无法使用 OleDB 驱动程序直接在 OpenQuery 中进行日期比较，因此要解决此问题，您必须将日期转换为 Julien 日期并比较这些数字。我有以下查询，我们试图在 MS SQL Server 中执行它（GPROD 是通过 OleDb 驱动程序的链接服务器）：
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:
如果我在 Oracle SQL Developer 中执行此查询，它会正常工作并返回数千行：
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_DATEfield is of type DATE and is nullable, if that matters.
该SHIP_DATE字段的类型为 DATE，并且可以为空（如果这很重要）。
Does anyone know what I can do to get this working through OpenQuery?
有谁知道我可以做些什么来通过 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:
我对 Julien Date 转换进行了测试，肯定发生了一些可疑的事情，但我不知道是什么原因造成的。如果我在 Oracle 中执行此操作：
select cast(to_number(to_char(to_date('01-JAN-2015') ,'J')) as numeric(10,0)) from dual
I get 2457024
我得到 2457024
If I execute this on SQL Server:
如果我在 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
我得到 1721443
回答by Pete
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.
通过 OpenQuery 和直接从 Oracle 给出相同的结果。
