1,105,406 Community Members

Changing the datatype of a Column

Member Avatar
Letscode
Junior Poster
175 posts since Feb 2005
Reputation Points: 1 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
wchitamb
Light Poster
31 posts since Jul 2005
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Letscode
Junior Poster
175 posts since Feb 2005
Reputation Points: 1 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Letscode
Junior Poster
175 posts since Feb 2005
Reputation Points: 1 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks Wchitamb.

Member Avatar
Estuardo
Newbie Poster
13 posts since Jul 2005
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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 SQL Server Books Online January 2004 Update
Good luck
Estuardo

Member Avatar
munna barik
Newbie Poster
1 post since Oct 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
mathewmoozh
Newbie Poster
22 posts since Nov 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Eagletalon
Junior Poster
135 posts since Mar 2011
Reputation Points: 34 [?]
Q&As Helped to Solve: 16 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Chris Schaller
Newbie Poster
1 post since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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...

bdivyadeepu
Newbie Poster
1 post since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

ALTER TABLE table ALTER COLUMN column_name new_data_type

ALTER TABLE table ALTER COLUMN column_name new_data_type

gunjain
Newbie Poster
1 post since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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

Member Avatar
ChrisHunter
Practically a Master Poster
625 posts since Feb 2011
Reputation Points: 120 [?]
Q&As Helped to Solve: 69 [?]
Skill Endorsements: 15 [?]
Featured
 
0
 

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.

manikandan5
Newbie Poster
1 post since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 
alter table tablename
alter column columnname datatype
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article