SQL Server - Change column from datetime to int

Asked By Ron Hinds on 21-Feb-07 06:38 PM
I want to change a column from datetime to int. I'm using this T-SQL:

ALTER TABLE WebPages
ALTER COLUMN EndDate int;

I get this result:

Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type int,
table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
function to run this query.

If I use Enterprise Manager the change happens without error. However, I
need to do this conversion as part of a larger script so I need to do it in
code. Can anyone tell me what EM is doing behind the scenes that allows this
to succeed? Thank you!




Kalen Delaney replied on 21-Feb-07 06:55 PM
Hi Ron

Please always state what version you are using.
I assume you are using SQL 2000 since you referred to Enterprise Manager. If
you trace what SQL Server is doing when you change datetime to int in EM,
you will see that it is actually recreating the entire table, selecting from
the old table using convert for the EndDate column, inserting into a new
table, dropping the original table and renaming the new table to the old
name. All indexes and triggers need to be rebuilt. This can be quite a
time-consuming process for a large table, but it is do-able. Just not with a
single statement.

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com