String data type to int - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

String data type to int

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-15 : 07:05:04

Hi i import a column into my db as nvarchar datatype as the column is a string it looks like this in the file "20.3" so it make me import it in as a varchar and not a int. I remove the " " so am left with 20.3 but i want to change the datatype to a int as, i import the data from the SQL table to an access table.

Any ideas ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 07:10:27

so you want to convert 20.3 to what value ?

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-15 : 07:14:33

i want to keep the number the same i like to convert the datatype from varchar to int so i can then move the data from SQL to my access data base were the column is of data type is number.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 07:27:24

How can you keep the value same when you are converting to INT? You will lose fraction part of it. Use either Decimal or Numeric data type with proper precision and scale.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-15 : 08:01:41

Yes of course i convert it to Decimal,

So here is my code, would this work ?

[STD COST ($)] is of data type nvarchar

update tbl_Shipdate_Temp
set [New STD COST ($)] = convert(Decimal, [STD COST ($)])

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 08:03:43

should also specify the precision and scale


update tbl_Shipdate_Temp
set [New STD COST ($)] = convert(Decimal(10,2), [STD COST ($)])

And why column name like [New STD COST ($)] ?

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-15 : 08:10:23

Don't worry about the name i change it just for testing ,

Am still getting a error

Error converting data type nvarchar to numeric.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 08:14:37

well it seems that you have some other data that is not able to convert to decimal like '' for example


update tbl_Shipdate_Temp
set [New STD COST ($)] = convert(Decimal(10,2), nullif([STD COST ($)], ''))

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-15 : 08:34:04

Yes i just did a distinct list and am getting other values into the column, i have to look at the DTS transformation it seem to be picking up wrong column for mapping.. i think its the text file i receive that the problem is with.

Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources