CODEWITHSUNDEEP
sqlsql-serverdatetimetimestampsql-timestamp
williamfaith
williamfaith

Reputation: 277

Convert timestamp into datetime and save to replace timestamp

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?

Upvotes: 1

Views: 1088

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12989

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.

  1. Create new table with just two columns: PrimaryKeycolumn, NewDateTime column
CREATE TABLE data_copy(PKColumn INT, NewDatetimeColumn DATETIME2);
  1. Load data into data_copy from original data table
 INSERT INTO data_copy(PKColumn, NewDatetimeColumn)
 SELECT PKColumn, DATEADD(second, [datetime] / 1000, '1970-01-01')
 FROM [database].[dbo].[data];
  1. Now, Update the datetime column to NULL values
 UPDATE [database].[dbo].[data]
    SET [datetime]= NULL;
  1. Now, Alter the datatype to DATETIME
--IF not null set accordingly
ALTER TABLE [database].[dbo].[data] ALTER COLUMN [datetime] DATETIME2 NULL;
  1. Now, Update the datetime values from the copy table
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

Upvotes: 1

Related Questions