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

Question

I have a column with a data type: date and time. But now I want to convert it to datatype varchar. Can I change the data type without dropping a column? If so, please explain how?

Answer 1

If ALTER COLUMN is not working.

It is not true that a variable column fails because it cannot do the desired conversion. In this case, the solution is to create a table of dummy tables, TableName_tmp, copy the data using a specialized conversion to the massive Insert command, delete the original table and rename the tmp table to the original table name. You will have to drop and recreate the foreign key constraints, and for performance, you probably want to create the keys after populating the tmp table.

Sounds a lot of work? This is actually not the case.

If you use SQL Server, you can make SQL Server Management Studio for you!

Raise the structure of the tables (right-click on the table and select "Change") and make all your changes (if the column transformation is illegal, just add a new column - you will close it in an instant). Then right-click the background in the Modify window and select Generate Change Script. In the window that appears, you can copy the script change to the clipboard.

Cancel the modification (in the end, you will want to check your script), and then paste the script into a new request window. Modify if necessary (for example, add your conversion by removing the field from the tmp table declaration), and now you have the script needed for the conversion.

Answer 2

MSDN says

ALTER TABLE mytable ALTER COLUMN mycolumn newtype 

Beware of the limitations of the ALTER COLUMN clause in the article.

Answer 3

 ALTER TABLE [table name] MODIFY COLUMN [column name] datatype 

Answer 4

 ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR(20) 

Answer 5

 ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR(20) 

Answer 6

It's simple! just enter the following query

alter table table_Name alter column column_name datatype

alter table Message alter column message nvarchar(1024);

he will work happy programming

Answer 7

With SQL Server 2008 and more, using this query:

 ALTER TABLE [RecipeInventorys] ALTER COLUMN [RecipeName] varchar(550) 

Answer 8

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

Answer 9

 ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150) 

Answer 10

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR (20) is ideal for changing the data type

Answer 11

ORACLE - change the table name table_name (column_name new_DataType);

Answer 12

ALTER TABLE yourtable MODIFY COLUMN data type yourcolumn

Answer 13

 ALTER TABLE table_name MODIFY (column_name data_type); 

Answer 14

 ALTER tablename MODIFY columnName newColumnType 

I'm not sure how it will handle changes from datetime to varchar, 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 request to populate the new column from the old one.

http://www.1keydata.com/sql/sql-alter-table.html

Answer 15

 alter table [table name] remove [present column name] to [new column name.