从链接到 Oracle 的 SQL Server 编写 SQL 查询时，如何指定日期文字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33228765/
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
How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
提问by Richard A
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
编辑：我刚刚发现了一个非常相似的问题：Forcing a SQL Remote Query to filter remote 而不是本地
回答by Shnugo
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:
感谢 lad2025 我想为完整性添加“完整”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
回答by Tidorith
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 年之前，这是一个好习惯。我在查看设置日历表以在查询中引用时遇到了这个问题。
回答by Lalit Kumar B
If the query is running on Oracle database, then I suggest use the ANSIdate literal which uses a fixed Format YYYY-MM-DD.
如果查询在 Oracle 数据库上运行，那么我建议使用使用固定格式YYYY-MM-DD的ANSI日期文字。
For example,
例如，
DATE '2015-10-20'
In Oracle, '20140701'is a stringand not a DATE. You might just be lucky to see an implicit data type conversionand get the result based on the locale-specific NLS settings of your client. You should always avoid it, and explicitlyconvert the string into date for date comparisons.
在 Oracle 中，'20140701'是字符串而不是DATE。您可能很幸运地看到了隐式数据类型转换并根据客户端的特定于语言环境的 NLS 设置获得结果。您应该始终避免它，并将字符串显式转换为日期以进行日期比较。
