从链接到 Oracle 的 SQL Server 编写 SQL 查询时，如何指定日期文字?

问题描述

我有一个链接到 Oracle 12.1 数据库的 SQL Server 12.0 数据库.

I have a SQL Server 12.0 database that is linked to an Oracle 12.1 database.

我想在 SQL Server 数据库中创建一个视图，该视图从按日期过滤的 Oracle 表中返回数据.Oracle 表在日期列上有一个索引.

I want to create a view in the SQL Server database that returns data from an Oracle table filtered by date. The Oracle table has an index on the date column.

成功的查询是:

select * from ORADB..SCHEMA.MYTABLE where MYDATE >= '20140701';

然而，这运行得很慢.我认为这是因为比较是在 SQL Server 中进行的，所以每一行都被返回.

However this runs very slowly. I assume it is because the comparison is taking place in SQL Server so every row is being returned.

如果我去:

DECLARE @earliest date = '20140701';
select * from ORADB..SCHEMA.MYTABLE where MYDATE >= @earliest;

然后它运行得很快，大概是因为正在将条件传递给 Oracle，因此正在使用表上的 Oracle 索引.

Then it runs fast, presumably because the condition is being passed to Oracle so the Oracle index on the table is being used.

我的问题是我想创建一个视图.我找不到使用第二版代码来创建视图的方法.如果我只是这样做:

My problem is that I want to create a view. I can't find a way of using the second version of the code to create a view. If I simply do:

create myview as select * from ORADB..SCHEMA.MYTABLE where MYDATE >= '20140701';

然后它运行缓慢.

SQL Server 将传递给 Oracle 的日期文字是否有其他格式，或者是否有其他解决方案?我还想知道这是否与创建 Oracle 链接时使用的参数有关.供参考:

Is there another format for the date literal that SQL Server will pass to Oracle, or is there another solution? I wondered also if it was to do with the parameters used in creating the link to Oracle. For reference they are:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORADB', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'DPDB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORADB',@useself=N'False',@locallogin=NULL,@rmtuser=N'MYUSER',@rmtpassword='#######'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORADB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

我刚刚发现了一个非常相似的问题:强制 SQL Remote 查询远程过滤而不是本地过滤

I just found a very similar question: Forcing a SQL Remote Query to filter remotely instead of locally

推荐答案

我更喜欢 ODBC 格式:

I prefer the ODBC format:

--DateTime
SELECT {ts'2015-09-20 12:30:00'}
--Time (however this comes with "today"-time)
SELECT {t'12:30:00'}
--Date
SELECT {d'2015-09-20'}
GO

简单的日期文字与文化无关...

The simple date literal is not culture independent...

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATETIME);--ERROR: there's no month "13"
GO

但它有效 - 然而 - 目标类型 DATE(这种差异相当奇怪......):

But it works - however - with target type DATE (this difference is rather weird...):

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATE);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATE);--ERROR: there's no month "13"
GO

感谢 lad2025 我想添加完整"的 ISO 8601 来完善它，它工作正常:

Thx to lad2025 I want to add for completness the "full" ISO 8601, which works fine:

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);
GO

这篇关于从链接到 Oracle 的 SQL Server 编写 SQL 查询时，如何指定日期文字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！