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

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

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

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.

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

A query that works successfully is:

成功运行的查询是:

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

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

然而,这运行得非常缓慢。我假设这是因为比较发生在SQL Server中,所以每行都被返回。

If I go:

如果我走:

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

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

然后它运行得很快,大概是因为条件传递给Oracle所以正在使用表上的Oracle索引。

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

Then it runs slowly.

然后它运行缓慢。

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:

是否存在SQL Server将传递给Oracle的日期文字的另一种格式,还是有另一种解决方案?我还想知道是否与创建Oracle链接时使用的参数有关。供参考,它们是:

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

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

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

3 个解决方案

#1

I prefer the ODBC format:

我更喜欢ODBC格式:

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

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

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

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

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

Thx to lad2025我想为completness添加“完整”ISO 8601,它工作正常:

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

#2

I suggest use the ANSI date literal which uses a fixed Format YYYY-MM-DD.

我建议使用ANSI日期文字,它使用固定的格式YYYY-MM-DD。

For example,

DATE '2015-10-20'

In Oracle, '20140701' is a string and not a DATE. You might just be lucky to see an implicit data type conversion and get the result based on the locale-specific NLS settings of your client. You should always avoid it, and explicitly convert the string into date for date comparisons.

在Oracle中,'20140701'是字符串而不是DATE。您可能只是幸运地看到隐式数据类型转换,并根据客户端的特定于语言环境的NLS设置获取结果。您应该始终避免它,并将字符串显式转换为日期比较日期。

#3

I would recommend using the full ISO 8601 format as suggested by @lad2025:

我建议使用@ lad2025建议的完整ISO 8601格式:

'2017-10-06T14:57:23'

This is superior to the ODBC format suggested by @Shnugo.

这优于@Shnugo建议的ODBC格式。

In SQL Server 2014 at least, the ODBC format will not work for dates prior to 1753-01-01 (e.g. those dates outside of the range of the old DATETIME data type), whereas the ISO 8601 format does.

至少在SQL Server 2014中,ODBC格式不适用于1753-01-01之前的日期(例如,旧DATETIME数据类型范围之外的日期),而ISO 8601格式则适用。

To test this yourself, try the following queries:

要自己测试,请尝试以下查询:

--This will work
DECLARE @DateISO DATE = '0001-01-01T00:00:00';
SELECT @DateISO;

--This will also work
DECLARE @DatetimeISO DATETIME2 = '0001-01-01T00:00:00';
SELECT @DatetimeISO;

--This will not work
DECLARE @DateODBC DATE = {D '0001-01-01'};
SELECT @DateODBC;

--This will also not work
DECLARE @DatetimeODBC DATETIME2 = {ts '0001-01-01 00:00:00'};
SELECT @DatetimeODBC;

Even if you don't think the dates you're working with will ever be before the year 1753, it's a good habit to be in. I ran into this while looking at setting up a calendar table to reference in queries.

即使你认为你所使用的日期永远不会在1753年之前,这也是一个很好的习惯。我试着设置一个日历表来参考查询。

#1