none
Change Column data type from Varchar to Date time RRS feed

  • Question

  • Hi,

    I have a oracle db with a date time column.

    I have imported the data into sql server with target column as varchar.

    Now my target tables(SQL SERVER DB) are now loaded with data.

    Now I want to change the column data type in target table sql server database from VARCHAR to DATETIME with out interrupting the data.

    I dont want to drop or delete the table as there is huge data residing in the tables.

    Please advice.

    Thanks in Advance,

    Sam.

    Wednesday, February 15, 2012 1:34 PM

Answers

  • Hi Sam SSIS,

    Regarding to your description, first you need to change the data in the existing column to be of a format that will allow the column data type to change.

    If so, you can try to use the command as below: "altertable<Table_name>altercolumn<column_name>datetime". But you need to alter table one by one.

    Hope this is useful for you.

    Regards, Amber zhang

    Friday, February 17, 2012 7:59 AM
    Moderator

All replies

  • Create another column in the table and define it as DATETIME... 

    WITH cte

    AS

    (

    SELECT * FROM tbl WHERE ISDATE(varcharcol)=1

    ) UPDATE cte SET newcol=varcharcol

     

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, February 15, 2012 1:42 PM
    Answerer
  • You should first check that the data in the column is OK:

    SELECT Convert(varcharCol as SmallDateTime) as convertedcol

    FROM YourTable;

    Then you could run an Alter Table command to change the column - see link below:

    http://msdn.microsoft.com/en-us/library/ms190273.aspx


    http://lqqsql.wordpress.com

    Wednesday, February 15, 2012 1:43 PM
  • Hi,

    I don't want to create/delete the table or columns.

    Is there any possible way that I could follow with out doing so.

    Thanks,

    Sam.

    Wednesday, February 15, 2012 1:47 PM
  • You can move the data into a temporary table (with a new column) with above WHERE condition, and  a key column . Then perform update  and move back the data from a temporsry table into the original one

    SELECT * INTO #tmp FROM tbl WHERE ISDATE(varchacol)=1

    ALTER TABLE #tmp ADD newcol DATETIME

    UPDATE #tmp SET newcol=varcharcol

    UPDATE tbl SET varcharcol=newcol FROM #tmp JOIN tbl t ON t.pk=#tmp.PK


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, February 15, 2012 1:53 PM
    Answerer
  • Hi Uri/Seth,

    Thanks for responding,

    I have millions of records available in the database. I just can't move it into a temporary table as it consumes lot of time.

    Also there are more than one table in my database where I need to apply this.

    Please advice,

    Sam.

    Wednesday, February 15, 2012 1:58 PM
  • Hi Sam SSIS,

    Regarding to your description, first you need to change the data in the existing column to be of a format that will allow the column data type to change.

    If so, you can try to use the command as below: "altertable<Table_name>altercolumn<column_name>datetime". But you need to alter table one by one.

    Hope this is useful for you.

    Regards, Amber zhang

    Friday, February 17, 2012 7:59 AM
    Moderator