Oracle时间戳到SQL Server DateTime [英] Oracle timestamp to sql server DateTime

问题描述

我有多个来自oracle数据库的语句，我需要在SQL Server中使用它们

I have multiple statements from oracle database and I need to use them in SQL Server

insert into COMENZI (NR_COMANDA, DATA, MODALITATE, ID_CLIENT, STARE_COMANDA, ID_ANGAJAT)
values (2456, to_timestamp('08-11-1998 07:53:25.989889', 'dd-mm-yyyy hh24:mi:ss.ff'), 'direct', 117, 0, 163);

insert into COMENZI (NR_COMANDA, DATA, MODALITATE, ID_CLIENT, STARE_COMANDA, ID_ANGAJAT)
values (2457, to_timestamp('01-11-1999 09:22:16.162632', 'dd-mm-yyyy hh24:mi:ss.ff'), 'direct', 118, 5, 159);

如何创建to_timestamp函数，该函数返回具有给定值的DateTime?

How can I create a function to_timestamp that returns a DateTime with the given value?

推荐答案

以下内容在SQL Server 2008中有效( SQL小提琴):

The following works in SQL Server 2008 (SQL Fiddle):

select convert(datetime, left(t, 10), 105) +
       convert(time, substring(t, 12, 12), 114)
from (select '01-11-1999 09:22:16.162632' as t) t;

具有讽刺意味的是，它在SQL Server 2012中不起作用.在那里，我认为您必须这样做:

Ironically, it doesn't work in SQL Server 2012. There, I think you have to do:

select dateadd(ms, datediff(ms, 0,  convert(datetime, substring(t, 12, 12), 114)),
               convert(datetime, left(t, 10), 105)
              )
from (select '01-11-1999 09:22:16.162632' as t) t;

请注意，在两种情况下，这都使用毫秒而不是微秒.我认为SQL Server不能提供如此精确的日期时间值.

Note in both cases, this uses milliseconds rather than microseconds. I don't believe SQL Server offers date time value with that much precision.

这篇关于Oracle时间戳到SQL Server DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！