Change column from datetime to int

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!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
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>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!
>
>

Change column from datetime to int

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!
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
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>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!
>
>