Reputation: 1015
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);
How can I create a function to_timestamp that returns a DateTime with the given value?
Upvotes: 2
Views: 11797
Reputation: 1
If you change this statement
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;
into
select convert(datetime, left(t, 10), 105) +
convert(datetime, substring(t, 12, 12), 114)
from (select '01-11-1999 09:22:16.162632' as t) t;
it will work correct with all SQL-Server versions
The error that datetime and time are incompatible types in the add-operator occurs only in SQL-Server 2012.
Upvotes: 0
Reputation: 1269513
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;
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;
Note in both cases, this uses milliseconds rather than microseconds. I don't believe SQL Server offers date time value with that much precision.
Upvotes: 2