varchar (20) to a number

[Chumley40]

I have a varchar (20) field with values like:
0.857142857142857

I need to convert it to a number (keeping the accuracy) but keep getting an arithmetic overflow error.

Can anyone help me?

Thanks!
TJ

 

[gmmastros]

When you say, "convert it to a number", what do you mean? There are several number types in SQL Server. There float, real, decimal/numeric, money, int, bigint, etc...


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chumley40]

any number type that will work for using to do calculations. I get an error on float, numeric, decimal

 

[gmmastros]

I don't get an error when I run your example value:

Declare @Blah varchar(20)
Set @Blah = '0.857142857142857'

Select Convert(float, @Blah)

Therefore, I assume there are values in this varchar(20) column that cannot be converted to a number. For example, if you had the value [!]'Seven'[/!], you would get a conversion error. To find these bad values, run the following query.

Select   YourColumnName
From     YourTable
Where    IsNumeric(YourColumnName) = 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chumley40]

Thanks
TJ