How do I specify a literal date when writing an SQL query with SQL Server related to Oracle?

Question

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

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

The request was successfully completed:

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

However, this is very slow. I assume this is because the comparison is happening in SQL Server, so every row is returned.

If I go:

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

Then it runs fast, presumably because the condition is passed to Oracle, so it uses the Oracle index in the table.

My problem is that I want to create a view. I cannot find a way to use the second version of the code to create the view. If I just do:

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

Then it works slowly.

Is there a different format for the date literal that SQL Server will migrate to Oracle, or is there another solution? I also wondered if this is due to the parameters used when creating the Oracle link. For reference, they:

 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 remote SQL query to filter remotely rather than locally

Answer 1

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 

A 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 no month "13" GO 

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

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

thanks to lad2025 I want to add for completeness 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 

Answer 2

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

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

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

In SQL Server 2014, at least the ODBC format will not work for dates until 1753-01-01 (for example, these dates are outside the range of the old DATETIME data type), while the ISO 8601 format does.

To verify 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 that the dates you work with will never exist until 1753, it’s a good habit to be. I came across this while looking at setting up a calendar table for links in queries.

Answer 3

If the query is executed in an Oracle database, then I suggest using the ANSI date literal , which uses the fixed format YYYY-MM-DD .

For instance,

 DATE '2015-10-20' 

In Oracle, '20140701' is a string, not a DATE . Perhaps you are just lucky to see the implicit data type conversion and get the result based on your client's NLS settings for a particular locale . You should always avoid this and explicitly convert the string to a date to compare dates.