Sql-server – Changing a column data type from datetime to float

datatypesddlsql server

I have a table with a float column. All values in this column for all records are NULL.
I want to change the data type to datetime.

I executed the query:

ALTER TABLE MyTable ALTER Column MyColumn DATETIME NULL

It works. But when I try to revert the changes by setting the data type to float:

ALTER TABLE MyTable ALTER Column MyColumn FLOAT NULL

I get the exception:

Implicit conversion from data type datetime to float is not allowed.
Use the CONVERT function to run this query.

Note that column MyColumn is null for all records.

Best Answer

With no data in the column you can just drop it and add it again.

alter table MyTable drop column MyColumn;

go

alter table MyTable add MyColumn float;

If you have data in your column and it makes sense to convert the values to a float value you can rename the column, add a new column, move the data using convert and then drop the old column.

exec sp_rename 'MyTable.MyColumn', 'Temp_MyColumn', 'COLUMN';

go

alter table MyTable add MyColumn float;

go

update MyTable 
set MyColumn = convert(float, Temp_MyColumn) 
where Temp_MyColumn is not null;

go

alter table MyTable drop column Temp_MyColumn;
Related Question