Unable to change a column from DATETIME to INT

  • Consider the following script

    CREATE TABLE Test_Table

    (

    Test_Column DATETIME

    )

    ALTER TABLE Test_Table ALTER COLUMN Test_Column INT

    When i execute this script, i get an error saying Msg 257, Level 16, State 3, Line 6

    Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

    Why am i getting this error even though it doesnot have any data in the table? And how do we re-write this using a CONVERT function?


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • There is no implicit conversion from DATETIME to INT. If your table has no data, drop it and recreate it. Otherwise, what you need to do is create the new structure on the side, drop all constraints from the old table, rename the old table, name the new table to the old table, migrate the data by hand, recreate the constraints on the new table, drop the old table.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (7/22/2010)


    There is no implicit conversion from DATETIME to INT. If your table has no data, drop it and recreate it. Otherwise, what you need to do is create the new structure on the side, drop all constraints from the old table, rename the old table, name the new table to the old table, migrate the data by hand, recreate the constraints on the new table, drop the old table.

    Actually, in my real table i had the values as NULL for the particular column and it was giving the same error. Then i changed the data type from DATETIME to VARCHAR and then from VARCHAR to INT and it worked. But i was just curious to know if there is some way to re-write the statement using CONVERT function as the error suggests.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • i also tried it . datatime --> varchar ---> int . it worked because sql server allows varchar to int conversion only incase when column contains null values but it gives error when column has some values

    try this

    CREATE TABLE Test_Table

    (

    Test_Column DATETIME

    )

    insert into Test_Table select getdate()

    ALTER TABLE Test_Table ALTER COLUMN Test_Column varchar(200)

    ALTER TABLE Test_Table ALTER COLUMN Test_Column int

    First ALTER will wok but second gives below error

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value 'Jul 23 2010 3:44AM' to data type int.

    The statement has been terminated.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Is there no straight forward answer?

    why we have to change to varchar then to INT?

  • My question, why are you converting a datetime column to an int column? If it is a date/time value use datetime data types.

  • My SSIS package has a Months column with 2 digit values but my table has Months column in Date datatype I want to convert

  • krismaly (6/5/2015)


    My SSIS package has a Months column with 2 digit values but my table has Months column in Date datatype I want to convert

    That's the wrong data type. If you want to store dates, use one of the datetime data types. If you want to store a quantity of months, use a numeric data type.

  • Thanks

  • krismaly (6/5/2015)


    Is there no straight forward answer?

    why we have to change to varchar then to INT?

    What are you trying to do?

    If the date is 01/01/2014, are you trying to store 01012014?

    If so, and int wont work because of the leading 0

    How about:

    ALTER TABLE MyTable DROP COLUMN DateColumn

    ALTER TABLE MyTable ADD DateColumn int

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • I used following statements and it worked fine

    ALTER TABLE Test_Table ALTER COLUMN Test_Column varchar(200)

    ALTER TABLE Test_Table ALTER COLUMN Test_Column int

    But I am looking for one statement not two

  • [font="Arial Black"]Hey, why are we trying to change a column declared as a DATETIME column to an INT? If it is a Date/Time value store it as such.[/font]

    Sorry for yelling but I felt my question was being ignored.

  • :-):-)

    You are fine

    You are helping

    :-):-)

  • krismaly (6/5/2015)


    :-):-)

    You are fine

    You are helping

    :-):-)

    I hope you aren't changing your datetime to an integer so you can store your dates as MMDDYYYY instead of strong them as dates.

    I don't see how ignoring the most basic question helps. So why are you changing it from a datetime to an integer?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply