Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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;
share|improve this answer
    
thanks Mikael............. It works for me –  Anirudha Aug 21 '13 at 6:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.