title: How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
body: <p>I have a SQL Server 12.0 database that is linked to an Oracle 12.1 database.</p>
  
  <p>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.</p>
  
  <p>A query that works successfully is:</p>
  
  <pre><code>select * from ORADB..SCHEMA.MYTABLE where MYDATE &gt;= '20140701';
  </code></pre>
  
  <p>However this runs very slowly. I assume it is because the comparison is taking place in SQL Server so every row is being returned.</p>
  
  <p>If I go:</p>
  
  <pre><code>DECLARE @earliest date = '20140701';
  select * from ORADB..SCHEMA.MYTABLE where MYDATE &gt;= @earliest;
  </code></pre>
  
  <p>Then it runs fast, presumably because the condition is being passed to Oracle so the Oracle index on the table is being used.</p>
  
  <p>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:</p>
  
  <pre><code>create myview as select * from ORADB..SCHEMA.MYTABLE where MYDATE &gt;= '20140701';
  </code></pre>
  
  <p>Then it runs slowly.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>EDIT: I just found a very similar question: <a href="https://stackoverflow.com/questions/1557285/forcing-a-sql-remote-query-to-filter-remotely-instead-of-locally">Forcing a SQL Remote Query to filter remotely instead of locally</a></p>