CODEWITHSUNDEEP
sql-servercastingdatetime2
ccuga
ccuga

Reputation: 3

How to convert nvarchar timestamp to datetime2 in SQL Server?

I've moved my data to a staging table and will eventually insert this data into my main table. I'm having issues converting nvarchar to datetime2. My nvarchar datetime columns have values that look like this: 2019-04-02T12:45:47.000-0400 . I want to convert this to datetime2 - 2019-04-02 12:45:47.0000000 .

How can I do this as simply altering the table isn't working for me?

Upvotes: 0

Views: 1072

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8819

The format of the date/time string you are using is not directly convertible to datetime2 nor datetimeoffset. If you didn't have the timezone offset you could use convert(datetime2, '2019-04-02T12:45:47.000', 126).

If you want to keep the timezone offset, try something like the following...

use StackOverflow;
go
drop table if exists dbo.Staging;
create table dbo.Staging (
  Foo varchar(30)
);

drop table if exists dbo.Main;
create table dbo.Main (
  Bar datetime2,
  Baz datetimeoffset
);

insert dbo.Staging values ('2019-04-02T12:45:47.000-0400');

-- 1. Replace the 'T' with space(1)
-- 2. Reformat the timezone offset (note the space before the sign)
update dbo.Staging
set Foo = replace(replace(Foo, 'T', ' '), '-0400', ' -04:00');

insert dbo.Main (Bar, Baz)
  select cast(Foo as datetime2), cast(Foo as datetimeoffset)
  from dbo.Staging;

select * from dbo.Main;

Which yields...

Bar                         Baz
--------------------------- ----------------------------------
2019-04-02 12:45:47.0000000 2019-04-02 12:45:47.0000000 -04:00

Upvotes: 0

Related Questions