+ Reply to Thread
Results 1 to 2 of 2

Change column from datetime to int

  1. 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!




  2. Re: Change column from datetime to int

    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!
    >
    >
    >




+ Reply to Thread