Member Avatar for Letscode

Hello,
I want to change the datatype of a field from Varchar to integer in T-SQL.

I tried something like Modify like you use in Oracle.

ALTER TABLE tablename
MODIFY COLUMNNAME Datatype.

But its not working in SQL server..

Any thoughts.

  • 11 Contributors
  • 12 Replies
  • 1K Views
  • 6 Years Discussion Span
  • Latest Post Latest Post by manikandan5

Dani AI

Generated

Short practical note: changing a VARCHAR column to INT will work in T‑SQL, but it will fail if any existing row cannot be converted. pointed out the T‑SQL route and used the GUI; hit the conversion error that happens when non‑numeric text, currency symbols, commas, or unexpected whitespace exist. The safe approach is to stage and validate data before you touch the original column.

A reliable workflow (test in a dev copy and back up first):

  • Add a nullable staging column of the target type.
  • Populate it with a safe conversion that does not abort the whole statement.
  • Inspect and fix any rows that failed conversion.
  • Drop the old column and rename the staging column.

Example (adjust names to your table/column):

ALTER TABLE dbo.YourTable ADD NewColInt INT NULL;

UPDATE dbo.YourTable
SET NewColInt = TRY_CONVERT(INT, OldColVarchar);

SELECT *
FROM dbo.YourTable
WHERE OldColVarchar IS NOT NULL
  AND TRY_CONVERT(INT, OldColVarchar) IS NULL;

-- optional cleaning before conversion:
UPDATE dbo.YourTable
SET NewColInt = TRY_CONVERT(INT, REPLACE(LTRIM(RTRIM(OldColVarchar)), ',', '') );

ALTER TABLE dbo.YourTable DROP COLUMN OldColVarchar;
EXEC sp_rename 'dbo.YourTable.NewColInt', 'OldColVarchar', 'COLUMN';

Notes and cautions: use TRY_CONVERT / TRY_CAST when available (newer SQL Server builds) because they return NULL instead of failing. On older servers you must validate (ISNUMERIC has known pitfalls), or cleanse strings (remove commas/currency, trim) before casting. If the column participates in indexes, FKs, PKs, or is an IDENTITY, drop/recreate constraints or perform the change via a new table and a controlled cutover. Always test on a copy and schedule maintenance for large tables. Thanks to and for the original pointers — staging and validation avoid the common conversion errors.

Recommended Answers

All 12 Replies

Member Avatar for wchitamb

i am not very sure is it different as in SQL where yu can go to the database that you want and then you go to the table and then you click the right side of yur mouse and go to design table and change it

Member Avatar for Letscode

I changed it. I went to enterprise Manager and went to design view of the table and changed the data type of the field.

Thanks anyway

Member Avatar for Letscode

Thanks Wchitamb.

Member Avatar for Estuardo

G'd evening!
In case you still need to change the table's column type from T-SQL , the sintax for MS SQL is:
ALTER TABLE table ALTER COLUMN column_name new_data_type
Ex.
ALTER TABLE MyTable ALTER COLUMN MyColumn NVARCHAR(20)

For more about the Alter Table sintax, read the books on line (BOL), here is the last update for SQL 2k
Good luck
Estuardo

Member Avatar for munna barik

thank u ESTUARDO for sending t-satatment on changing data type

Member Avatar for mathewmoozh

ALTER TABLE table ALTER COLUMN column_name new_data_type
this will definitely work i got answer
thank you Estuardo

Edited by mathewmoozh because: n/a
Member Avatar for Eagletalon

1 Question... how does 1 do this for a column with the data type char if you want tot take it to decimal... gives me an error on the conversion....

"Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."

is there an additional way to write in a CONVERT for the data included in the field?

Member Avatar for Chris Schaller

I know this is old, but you know good old Google...

You must first parse the data in the column to ensure that all values will 'implicitly' convert to the new data type.
simple sql to review all rows that will not convert:

SELECT <column> WHERE IsNumeric(<column>) = 0

Once all values in the column can be implicitly converted to the new data type, the alter column statement will complete.

For other readers, note that certain data types and values can only be implicitly converted to specific types and this method may require first a conversion to varchar and then to your desired type.
For some types, you will not be able to use implicit conversion at all, in these cases you will may need to do it the MS way... write the data out to a temp table, drop rows, alter table then write back the rows...

Member Avatar for bdivyadeepu

ALTER TABLE table ALTER COLUMN column_name new_data_type

ALTER TABLE table ALTER COLUMN column_name new_data_type

Member Avatar for gunjain

sir i have typed the query you refered but there is error near datatypr

Member Avatar for ChrisHunter

Some data types need you to define the seed number or how many decimal places you want to store. Taking this into account do some research on the decimal datatype as you have to define how many decimal places you want to store if you want to use decimal datatype.

Member Avatar for manikandan5 
alter table tablename
alter column columnname datatype
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.