Oracle timestamp to sql server DateTime

Question

I am having multiple statements from the oracle database and I want to use them in the 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);

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

Answer 1

You can execute the following code in SQL Server 2008:

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;

However, it doesn't work in SQL Server 2012. There, I think you need to use the following:

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;

Note in both the cases, this uses milliseconds instead of microseconds. I don't think that the SQL Server offers date-time value with that much precision.

Looking for SQL Tutorial? Join the SQL Training course to gain more knowledge on SQL.