• <bdo id='KAiem'></bdo><ul id='KAiem'></ul>
  • <legend id='KAiem'><style id='KAiem'><dir id='KAiem'><q id='KAiem'></q></dir></style></legend>
    <tfoot id='KAiem'></tfoot>
  • <small id='KAiem'></small><noframes id='KAiem'>

        <i id='KAiem'><tr id='KAiem'><dt id='KAiem'><q id='KAiem'><span id='KAiem'><b id='KAiem'><form id='KAiem'><ins id='KAiem'></ins><ul id='KAiem'></ul><sub id='KAiem'></sub></form><legend id='KAiem'></legend><bdo id='KAiem'><pre id='KAiem'><center id='KAiem'></center></pre></bdo></b><th id='KAiem'></th></span></q></dt></tr></i><div id='KAiem'><tfoot id='KAiem'></tfoot><dl id='KAiem'><fieldset id='KAiem'></fieldset></dl></div>

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

        How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?(从链接到 Oracle 的 SQL Server 编写 SQL 查询时,如何指定日期文字?)
          <tbody id='locXQ'></tbody>

          <tfoot id='locXQ'></tfoot>

            <i id='locXQ'><tr id='locXQ'><dt id='locXQ'><q id='locXQ'><span id='locXQ'><b id='locXQ'><form id='locXQ'><ins id='locXQ'></ins><ul id='locXQ'></ul><sub id='locXQ'></sub></form><legend id='locXQ'></legend><bdo id='locXQ'><pre id='locXQ'><center id='locXQ'></center></pre></bdo></b><th id='locXQ'></th></span></q></dt></tr></i><div id='locXQ'><tfoot id='locXQ'></tfoot><dl id='locXQ'><fieldset id='locXQ'></fieldset></dl></div>

            <legend id='locXQ'><style id='locXQ'><dir id='locXQ'><q id='locXQ'></q></dir></style></legend>

                <bdo id='locXQ'></bdo><ul id='locXQ'></ul>

                <small id='locXQ'></small><noframes id='locXQ'>

                • 本文介绍了从链接到 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 查询时,如何指定日期文字?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM(Oracle PL/SQL - 使用自定义 SQLERRM 引发用户定义的异常)
                  Oracle: is there a tool to trace queries, like Profiler for sql server?(Oracle:是否有跟踪查询的工具,例如用于 sql server 的 Profiler?)
                  SELECT INTO using Oracle(使用 Oracle SELECT INTO)
                  How to handle Day Light Saving in Oracle database(如何在 Oracle 数据库中处理夏令时)
                  PL/SQL - Use quot;Listquot; Variable in Where In Clause(PL/SQL - 使用“列表Where In 子句中的变量)
                  Oracle: Import CSV file(Oracle:导入 CSV 文件)

                    <small id='yxZIf'></small><noframes id='yxZIf'>

                    1. <tfoot id='yxZIf'></tfoot>
                      <legend id='yxZIf'><style id='yxZIf'><dir id='yxZIf'><q id='yxZIf'></q></dir></style></legend>
                    2. <i id='yxZIf'><tr id='yxZIf'><dt id='yxZIf'><q id='yxZIf'><span id='yxZIf'><b id='yxZIf'><form id='yxZIf'><ins id='yxZIf'></ins><ul id='yxZIf'></ul><sub id='yxZIf'></sub></form><legend id='yxZIf'></legend><bdo id='yxZIf'><pre id='yxZIf'><center id='yxZIf'></center></pre></bdo></b><th id='yxZIf'></th></span></q></dt></tr></i><div id='yxZIf'><tfoot id='yxZIf'></tfoot><dl id='yxZIf'><fieldset id='yxZIf'></fieldset></dl></div>
                        <bdo id='yxZIf'></bdo><ul id='yxZIf'></ul>

                              <tbody id='yxZIf'></tbody>