Changing a column data type from datetime to float

Question

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.

Answer 1

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;