I have a VARCHAR column that looks like this:
Created_DTM
-----------
2020-02-05 16:26:45.00Z
2020-02-16 08:55:52.00Z
2020-04-24 15:24:01.00Z
2020-06-13 22:14:18.00Z
How do I write a script that will create a new column called Created_DTM_Converted and fill each record with a value so that the zulu (Z) character is gone whilst subtracting 5 hours to match my timezone?
What I've tried:
This query gives me the exact values I need:
SELECT dateadd(hh, -5, convert(datetime2(2), Created_DTM, 120)) AS Created_DTM_Converted
FROM dbo.table
This is the output:
Created_DTM_Converted
-----------
2020-02-05 11:26:45.00
2020-02-16 03:55:52.00
2020-04-24 10:24:01.00
2020-06-13 17:14:18.00
But I don't know how to write a script that can be executed once and will make this a permanently added column. How do I go about doing so?
Since you mentioned in the commentary that you intend to calculate the new column from the old column, and intend to drop the old column later, you won't be able to use Tim's answer of a computed column.
Therefore you will need to execute this process in two steps. First add the column, then populate it with values. You can drop the original column later as a third step.
alter table dbo.table add Created_DTM_Converted datetime2(2) null;
go
update dbo.table
set Created_DTM_Converted = dateadd(hh, -5, convert(datetime2(2), Created_DTM, 120));
Once you have populated the column, you can optionally alter its definition to not allow nulls:
alter table dbo.table alter column Created_DTM_Converted datetime2(2) not null;