SQL Server Performance Forum – Threads Archive
changing datatype
I have a table which has a field of nvarchar datatype. It already contains numbers. I want to change the datatype to int. It is not allowing me. I am getting this error:Error converting data type nvarchar to int. Can you please let me know how to overcome this problem. salimlallas
If you want to change data type column, only way is to create a new column, convert from nvarchar to int and then rename de new column. Additional , see CONVERT in BOL.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
We should add that certain conversions from similar data types are allowed and easy to be performed in SQLEM. By the way, sometimes you can find some problems with databases set to a "compatibility level" different than 8.0 when performing these operations.
Sounds like not all data in that column are numbers. Conversion should happen smoothly
declare @a nvarchar
set @a = ‘1’
select cast(@a as int)
works.
However when you have decimal places the conversion to fails:
declare @a nvarchar(3)
set @a = ‘1.1’
select cast(@a as int) Server: Nachr.-Nr. 245, Schweregrad 16, Status 1, Zeile 3
Syntaxfehler beim Konvertieren des nvarchar-Wertes ‘1.1’ in eine Spalte vom Datentyp int.
Maybe you use ISNUMERIC() to determine whether your data can be evaluated as as valid numeric type. ———————–
–Frank
http://www.insidesql.de
———————–
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by LuisMartin</i><br /><br />If you want to change data type column, only way is to create a new column, convert from nvarchar to int and then rename de new column.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Not exactly, Luis.<br />Try this:<br /><pre><br />set nocount on<br />create table mytable<br />(<br />col1 nvarchar(3)<br />)<br />insert into mytable(col1) values(‘1’)<br />select * from mytable<br />select <br />cast(c.column_name as char(30))<br />, cast(c.data_type as char(30)) <br />from information_schema.columns c <br />join information_schema.tables t on c.table_name = t.table_name<br />where t.table_name = ‘mytable'<br /><br /><br />alter table mytable alter column col1 int<br />go<br />select * from mytable<br />select <br />cast(c.column_name as char(30))<br />, cast(c.data_type as char(30)) <br />from information_schema.columns c <br />join information_schema.tables t on c.table_name = t.table_name<br />where t.table_name = ‘mytable'<br />drop table mytable<br />set nocount off<br /><br />col1 <br />—- <br />1<br /><br /> <br />—————————— —————————— <br />col1 nvarchar <br /><br />col1 <br />———– <br />1<br /><br /> <br />—————————— —————————— <br />col1 int <br /></pre><br /><br />Sometimes it works [<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
You are rigth Frank, I suppose (my fault) Saliml was using EM. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>