How to change the data type of a column without dropping the column with query

Question

I have a column which has a datatype   datetime  But now i want to convert it to datatype varchar  Can i alter the datatype without droppping the column  If yes  then please explain how

User · Answer

ALTER TABLE yourtable MODIFY COLUMN yourcolumn datatype

User · Answer

This work for postgresql 9 0 3   alter table  table name  ALTER COLUMN  column name  TYPE  character varying     http   www postgresql org docs 8 0 static sql-altertable html

User · Answer

ALTER TABLE  table name  MODIFY COLUMN  column name  datatype

User · Answer

alter table  table name  remove  present column name  to  new column name

User · Answer

ALTER tablename MODIFY columnName newColumnType   I m not sure how it will handle the change from datetime to varchar though  so you may need to rename the column  add a new one with the old name and the correct data type  varchar  and then write an update query to populate the new column from the old   http   www 1keydata com sql sql-alter-table html

User · Answer

If ALTER COLUMN doesn t work   It is not unusual for alter column to fail because it cannot make the transformation you desire  In this case  the solution is to create a dummy table TableName tmp  copy the data over with your specialized transformation in the bulk Insert command  drop the original table  and rename the tmp table to the original table s name   You ll have to drop and recreate the Foreign key constraints and  for performance  you ll probably want to create keys after filling the tmp table   Sound like a lot of work  Actually  it isn t   If you are using SQL Server  you can make the SQL Server Management Studio do the work for you     Bring up your table structure  right-click on the table column and select  Modify    Make all of your changes  if the column transformation is illegal  just add your new column - you ll patch it up in a moment     Right-click on the background of the Modify window and select  Generate Change Script    In the window that appears  you can copy the change script to the clipboard  Cancel the Modify  you ll want to test your script  after all  and then paste the script into a new query window    Modify as necessary  e g  add your transformation while removing the field from the tmp table declaration  and you now have the script necessary to make your transformation

User · Answer

it s simple   just type bellow query  alter table table Name alter column column name datatype alter table Message alter column message nvarchar 1024    it will work happy programming

User · Answer

With SQL server 2008 and more  using this query   ALTER TABLE  RecipeInventorys  ALTER COLUMN  RecipeName  varchar 550

User · Answer

If ALTER COLUMN doesn t work   It is not unusual for alter column to fail because it cannot make the transformation you desire  In this case  the solution is to create a dummy table TableName tmp  copy the data over with your specialized transformation in the bulk Insert command  drop the original table  and rename the tmp table to the original table s name   You ll have to drop and recreate the Foreign key constraints and  for performance  you ll probably want to create keys after filling the tmp table   Sound like a lot of work  Actually  it isn t   If you are using SQL Server  you can make the SQL Server Management Studio do the work for you     Bring up your table structure  right-click on the table column and select  Modify    Make all of your changes  if the column transformation is illegal  just add your new column - you ll patch it up in a moment     Right-click on the background of the Modify window and select  Generate Change Script    In the window that appears  you can copy the change script to the clipboard  Cancel the Modify  you ll want to test your script  after all  and then paste the script into a new query window    Modify as necessary  e g  add your transformation while removing the field from the tmp table declaration  and you now have the script necessary to make your transformation

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

This work for postgresql 9 0 3   alter table  table name  ALTER COLUMN  column name  TYPE  character varying     http   www postgresql org docs 8 0 static sql-altertable html

User · Answer

ALTER TABLE  table name  ALTER COLUMN  column name  varchar 150

User · Answer

it s simple   just type bellow query  alter table table Name alter column column name datatype alter table Message alter column message nvarchar 1024    it will work happy programming

User · Answer

With SQL server 2008 and more  using this query   ALTER TABLE  RecipeInventorys  ALTER COLUMN  RecipeName  varchar 550

User · Answer

MSDN says  ALTER TABLE mytable ALTER COLUMN mycolumn newtype   Beware of the limitations of the ALTER COLUMN clause listed in the article

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

ALTER TABLE table name MODIFY  column name data type

User · Answer

ORACLE - Alter table table name modify column name new DataType

User · Answer

MSDN says  ALTER TABLE mytable ALTER COLUMN mycolumn newtype   Beware of the limitations of the ALTER COLUMN clause listed in the article

User · Answer

MSDN says  ALTER TABLE mytable ALTER COLUMN mycolumn newtype   Beware of the limitations of the ALTER COLUMN clause listed in the article

User · Answer

ALTER tablename MODIFY columnName newColumnType   I m not sure how it will handle the change from datetime to varchar though  so you may need to rename the column  add a new one with the old name and the correct data type  varchar  and then write an update query to populate the new column from the old   http   www 1keydata com sql sql-alter-table html

User · Answer

alter table  table name  remove  present column name  to  new column name

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20  this is perfect for change to datatype

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

ORACLE - Alter table table name modify column name new DataType

User · Answer

ALTER TABLE  table name  MODIFY COLUMN  column name  datatype

User · Answer

If ALTER COLUMN doesn t work   It is not unusual for alter column to fail because it cannot make the transformation you desire  In this case  the solution is to create a dummy table TableName tmp  copy the data over with your specialized transformation in the bulk Insert command  drop the original table  and rename the tmp table to the original table s name   You ll have to drop and recreate the Foreign key constraints and  for performance  you ll probably want to create keys after filling the tmp table   Sound like a lot of work  Actually  it isn t   If you are using SQL Server  you can make the SQL Server Management Studio do the work for you     Bring up your table structure  right-click on the table column and select  Modify    Make all of your changes  if the column transformation is illegal  just add your new column - you ll patch it up in a moment     Right-click on the background of the Modify window and select  Generate Change Script    In the window that appears  you can copy the change script to the clipboard  Cancel the Modify  you ll want to test your script  after all  and then paste the script into a new query window    Modify as necessary  e g  add your transformation while removing the field from the tmp table declaration  and you now have the script necessary to make your transformation

User · Answer

ALTER TABLE yourtable MODIFY COLUMN yourcolumn datatype

User · Answer

MSDN says  ALTER TABLE mytable ALTER COLUMN mycolumn newtype   Beware of the limitations of the ALTER COLUMN clause listed in the article

User · Answer

ALTER tablename MODIFY columnName newColumnType   I m not sure how it will handle the change from datetime to varchar though  so you may need to rename the column  add a new one with the old name and the correct data type  varchar  and then write an update query to populate the new column from the old   http   www 1keydata com sql sql-alter-table html

User · Answer

ALTER tablename MODIFY columnName newColumnType   I m not sure how it will handle the change from datetime to varchar though  so you may need to rename the column  add a new one with the old name and the correct data type  varchar  and then write an update query to populate the new column from the old   http   www 1keydata com sql sql-alter-table html

User · Answer

If ALTER COLUMN doesn t work   It is not unusual for alter column to fail because it cannot make the transformation you desire  In this case  the solution is to create a dummy table TableName tmp  copy the data over with your specialized transformation in the bulk Insert command  drop the original table  and rename the tmp table to the original table s name   You ll have to drop and recreate the Foreign key constraints and  for performance  you ll probably want to create keys after filling the tmp table   Sound like a lot of work  Actually  it isn t   If you are using SQL Server  you can make the SQL Server Management Studio do the work for you     Bring up your table structure  right-click on the table column and select  Modify    Make all of your changes  if the column transformation is illegal  just add your new column - you ll patch it up in a moment     Right-click on the background of the Modify window and select  Generate Change Script    In the window that appears  you can copy the change script to the clipboard  Cancel the Modify  you ll want to test your script  after all  and then paste the script into a new query window    Modify as necessary  e g  add your transformation while removing the field from the tmp table declaration  and you now have the script necessary to make your transformation

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20  this is perfect for change to datatype

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

ALTER TABLE  table name  ALTER COLUMN  column name  varchar 150

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

ALTER TABLE table name MODIFY  column name data type

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

User · Answer

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR 20

[sql-server] How to change the data type of a column without dropping the column with query?

Examples related to sql-server