How to change column datatype in SQL Server and convert previous type without losing data

Question

I have a SQL Server database and I'd like to change column types to reduce it's size:

I have a string Name Varchar(100) and want to convert it to Varchar(50) AND I have to move data to it (data will fit into varchar(50))
I have a Varchar(30) that represents time as epoch time (eg. 1508752574). I want to change the type to Datetime and convert data form Varchar(30) to Datetime and then put it in Datetime

Can I do it without losing data?

Changing a `varchar(100)` to `varchar(50)` will **not** reduce the size of the table. — , Oct 23 '17 at 10:05
Ah ok. And what about 2) ? ** I'll be also easier to read the time — , Oct 23 '17 at 10:07

score 1 · Answer 1 · edited May 11 '22 at 08:27

Point 1

ALTER TABLE dbo.table_name ALTER COLUMN name VARCHAR(50);

Point 2 You would need to add another column of type Datetime and update the column with the Datetime equivalent of the epoch_time_col.

ALTER TABLE dbo.table_name ADD epoch_time_dt DATETIME NULL;
UPDATE dbo.table_name SET epoch_time_dt = DATEADD(SECOND, epoch_time_col, '19700101');

If you need, you can then drop the old column

ALTER TABLE dbo.table_name DROP COLUMN epoch_time_col ;

score 0 · Answer 2 · answered Oct 23 '17 at 10:11

0

Create new columns:

Alter table MyTable
add NewColumn1 varchar(50);

Alter table MyTable
add NewColumn2 datetime;

Fill with data:

update MyTable
set NewColumn1 = left(Name,50),
    NewColumn2 = dateadd(s, epochcol1, '19700101');

You can then drop the old columns and rename the new ones

answered Oct 23 '17 at 10:11

JohnHC

Thanks that helped! I only had to add conversion from string to int (as epochcol1) -> CONVERT(INT, Date) – Oct 23 '17 at 10:21

score 0 · Answer 3 · answered Oct 23 '17 at 10:13

I don't see any loss of data issue..

I have a string Name Varchar(100) and want to convert it to Varchar(50) AND I have to move data to it (data will fit varchar(50))

There will be no data loss,if your data fits in varchar(50) data type,if the data doesn't fit the insert will fail and you can rectify that

Also i don't see any issue with data loss in converting your epoch value to datetime with below approach,since you are just adding seconds

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

3 Answers3