将Oracle Datetime格式的查询转换为MS SQL Server格式

Question

我有一个Oracle查询

SELECT to_timestamp('29-03-17 03:58:34.312000000 PM','DD-MM-RR HH12:MI:SS.FF AM') 
FROM DUAL

我希望转换为SQL Server，需要保留Oracle日期字符串，即'29-03-17 03:58:34.312000000 PM'。

SELECT 
    CONVERT(DATETIME, REPLACE(REPLACE('29-03-2017 03:58:34.312000000 PM','-', '/'),'000000 ', ''), 131)

我尝试了上面的查询，因为131格式与“29-03-17 03:58:34.312000000 PM”的格式“dd/mm/yyyy hh:mi:ss:mmmAM”非常相似，但唯一的区别在于年份。

在Oracle中，年份为17，在SQL Server中，年份为2017。我需要在年份前加上20，使其成为2017。该查询将其转换为伊斯兰教历日期时间格式。我需要它以公历日期时间格式。

这是文档。

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

我需要将以Oracle格式表示的字符串日期转换为SQL Server等效日期。是否有一种方法可以在转换函数中指定格式，例如“dd/mm/yyyy hh:mi:ss:mmmAM”，而不是指定日期格式代码（如131、101、102）？

- Stupid Dream

---

SQL-Server 的哪个版本？ - Shnugo

你能改变TO_CHAR函数的格式掩码吗？如果可以，那么你可以使用ISO 8601 'yyyy-mm-ddThh:mm:ss.mmm'格式掩码，它将允许你直接将Oracle日期的字符表示转换为Microsoft SQL Server的DATETIME数据类型。这对于Microsoft SQL Server 2005及以上版本是适用的。 - Zanjato

修改在Oracle中使用的格式掩码，以生成方便转换的字符串。顺便提一下，为了实现所需的精度级别，您需要使用datetime2。如果所有这些都无法帮助，请考虑阅读此处的答案 https://dev59.com/b4jca4cB1Zd3GeqP4fqU - Paul Maxwell

Answer 1

您可以尝试像这样操作：

DECLARE @oracleDT VARCHAR(100)='29-03-17 03:58:34.312000000 PM';
SELECT CAST('<x>' + @oracleDT + '</x>' AS XML).value(N'(/x/text())[1]','datetime');

看起来，XML隐式地能够正确地执行此操作...

编辑：以上内容与文化有关！

在我的（德国）系统上运行良好，但是如果您设置了正确的日期格式，您可以强制执行此操作（请注意当前工作的副作用！）

尝试这个，然后删除--以尝试替代日期格式。或者尝试使用GERMAN：

SET LANGUAGE ENGLISH;
SET DATEFORMAT mdy;
--SET DATEFORMAT ymd;
--SET DATEFORMAT dmy;
DECLARE @oracleDT VARCHAR(100)='01-02-03 03:58:34.312000000 PM';
SELECT CAST('<x>' + @oracleDT + '</x>' AS XML).value(N'(/x/text())[1]','datetime');

另一种方法

你可以将字符串分成多个部分，并构建一个可转换的格式，例如：

DECLARE @oracleDT VARCHAR(100)='29-03-17 03:58:34.312000000 PM';
WITH AllParts(Casted) AS
(
    SELECT CAST('<x>' + REPLACE(REPLACE(REPLACE(REPLACE(@oracleDT,'.','-'),' ','-'),':','-'),'-','</x><x>') + '</x>' AS XML)
)  
SELECT CONVERT
      (DATETIME,
               DATENAME(MONTH,'2000'+Casted.value(N'x[2]/text()[1]','nvarchar(max)')+'01') + ' '
             + Casted.value(N'x[1]/text()[1]','nvarchar(max)') + ' '
     + N'20' + Casted.value(N'x[3]/text()[1]','nvarchar(max)') + ' '
             + Casted.value(N'x[4]/text()[1]','nvarchar(max)') + ':'
             + Casted.value(N'x[5]/text()[1]','nvarchar(max)') + ':'
             + Casted.value(N'x[6]/text()[1]','nvarchar(max)') + ':'
             + LEFT(Casted.value(N'x[7]/text()[1]','nvarchar(max)'),3)
             + Casted.value(N'x[8]/text()[1]','nvarchar(max)'),109)
FROM AllParts

- Shnugo

Answer 2

虽然我不太理解为什么要使用不适合转换的字符串格式，但您可以将字符串分成几部分，然后通过将这些部分相加来构建它。基础部分是前8个字符，使用格式样式5转换为datetime2。

select
      t
    , convert(varchar, converted ,121) converted
from (
        select '29-03-17 03:58:34.312000000 PM' as t
     ) t
cross apply (
    select 
        convert(datetime2,substring(t,1,8),5) dt2
      , case when right(t,2) = 'PM' then convert(smallint,substring(t,10,2)) + 12
             else convert(smallint,substring(t,10,2))
        end                                 hh
      , convert(smallint,substring(t,13,2)) mi
      , convert(smallint,substring(t,16,2)) ss
      , convert(int,substring(t,19,9))      ns
      ) ca
cross apply (
    select
        dateadd(hh,hh,dateadd(mi,mi,dateadd(ss,ss,dateadd(ns,ns,dt2))))
        as converted
      ) ca2
;

请注意，我能够在第一个交叉应用程序（dt1、hh、mi、ss、ns）中使用列别名来形成转换后的datetime2值。

+--------------------------------+-----------------------------+
|               t                |          converted          |
+--------------------------------+-----------------------------+
| 29-03-17 03:58:34.312000000 PM | 2017-03-29 15:58:34.3120000 |
+--------------------------------+-----------------------------+

请看: http://rextester.com/DZJ42703

- Paul Maxwell