How can I convert my float field into a Decimal in sql server?

Question

I want to convert my float field into a decimal field; I want a precision of 11,2 in my decimal field, but when I tried to change the type of my field(example: Amount) I get an error: "Arithmetic overflow error converting float to data type numeric. The statement has been terminated." My field is decimal(11,2) at the table, and my max and min values are: 1,603,837,393.70 < -- > -1,688,000,000.00(amount).

I created a temporary table to take out the data and changed the structure of my fields and that worked o.k. but now I need to put the data back (temporary table is still having the original float fields), and I just simply cannot make it insert values with a insert select statement.

Since my values don't exceed the range of my type I wonder why is not even possible to cast on a select statement like this:

select Id,AccountId, cast(Amount as decimal(12,2)) as Amount,
cast(AmountB as decimal(12,2)) as  AmountB
FROM myTable

I cannot identify the reason to not convert my field.

Answer 1

Check the field types. Are they right?

Answer 2

Your question is simply confusing. You say "My field is decimal(11,2) at the table," but this contradicts what you say about the max and min values, which aren't within the range of decimal(11,2). If the error you quote comes from the SELECT query you provide, then either Amount or AmountB has a value outside the range of values that can be represented by the type decimal(12,2): -9,999,999,999.99 to 9,999,999,999.99.

You will discover this if you run the following query:

select
  min(Amount) as minAmount,
  max(Amount) as maxAmount,
  min(AmountB) as minAmountB,
  max(AmountB) as maxAmountB
from myTable

Answer 3

But a value of "1,603,837,393.70" would require decimal(12,2) - 12 digits in all, 2 after the decimal point.

Maybe you misinterpreted the way the decimal(11,2) works? This would mean total of 11 digits - 9 to the left, 2 to the right of the decimal point.

See the MSDN docs for decimal and numeric types:

decimal[ (p[ , s] )] and numeric[ (p[, s] )]

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.

Marc