Arithmetic Overflow

[Mongr1l]

I'm trying to convert a varchar field into a decimal(22,6) field, but I keep getting an Arithmetic Overflow error. This is because some of the values look like this:

9.6666666666666666666666666666666666666666666666666666



If tried using ROUND(), but it doesn't work since I need to convert into a number field first and ROUND doesn't work with FLOAT.

What should I do?

mongr1l

 

[SQLSister]

Never use float as a datatype, it is not exact. Specify decimal with a few more places beyond the decimal than you need (especially if you are planning to do any multiplication ro division!)
First things first, why are you storing numbers in varchar? Once you get these converted, you really should consider changing the datatype to the proper one.

Questions about posting. See faq183-874

 

[bborissov]

SELECT CONVERT(decimal(22,6), LEFT(MyVarcharFld,CHARINDEX('.',MyVarcharFld)+6))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Try using the 'left' function, like this...

Declare @Temp Decimal(22,6)

Set @Temp = Left('9.6666666666666666666666666666666666666666666666666666', 22)

Select @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Mongr1l]

I didn't store it this way. The file was given to me in this fashion.

Some goofy dba in Jersey gave me this file.

mongr1l

 

[gmmastros]

I happen to know that SQLDenis is a dba in Jersey. Was it him?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Ah then ask them to give it to you correctly! I convert files all day long and have to send many of them back to the originator until they give me what I need. However, the left function should work for you if they can't figure out how to give you a non float number.

Questions about posting. See faq183-874