Convert timestamp into datetime and save to replace timestamp

Question

I'm using a SQL Server database and I have a datetime column.

SELECT
    [datetime]
FROM [database].[dbo].[data]

datetime
1584538200000
1584538260000
.............
1584538620000

Now I've already known how to convert the timestamp into datetime:

SELECT DATEADD(second, [datetime] / 1000, '1970-01-01')
FROM [database].[dbo].[data]

But my question is how to save this converted datetime to replace old timestamp in database?

Thank you for reply ,it is float,but I have no idea which one is better. — williamfaith, Mar 26 '20 at 04:17
If you want to store a `datetime` then the **better* datatype is `datetype` or these days `datetime2`, so if it was me I would be considering creating a new column and storing the date correctly. — Dale K, Mar 26 '20 at 04:26
Dear friend,can you help me with this question https://stackoverflow.com/questions/60868459/find-and-remove-duplicate-rows-from-a-sql-server-table — williamfaith, Mar 26 '20 at 13:32

score 1 · Accepted Answer · answered Mar 26 '20 at 04:37

It is preferable to store the values in the right datatype i.e., datetime or datetime2 (as suggested by @Dale K.

I would suggest you to follow below steps.

CREATE TABLE data_copy(PKColumn INT, NewDatetimeColumn DATETIME2);

 INSERT INTO data_copy(PKColumn, NewDatetimeColumn)
 SELECT PKColumn, DATEADD(second, [datetime] / 1000, '1970-01-01')
 FROM [database].[dbo].[data];

 UPDATE [database].[dbo].[data]
    SET [datetime]= NULL;

--IF not null set accordingly
ALTER TABLE [database].[dbo].[data] ALTER COLUMN [datetime] DATETIME2 NULL;

UPDATE d
SET d.[datetime] = c.datetimecolumn
FROM [database].[dbo].[data] AS d
JOIN [database].[dbo].[data_copy] AS c
ON d.PKColumn = c.PKColumn

1 Answers1