cast float to numeric

Question

Hi!
I import transactions from a table where the amount column is set to Float and some values in the column is scientific numbers (example below). I need to Cast the float column to decimal values but i cant find any solution on how to do that. Is there any solution on how to do that?
Br Arne

Answer 1

If you are converting it to decimal(38, 30). It is trying to convert it to a decimal with 8 digits to the left of the decimal point and 30 digits to the right of the decimal point. Which means any number greater than or equal to 100,000,000 or less than or equal to -100,000,000 would give you that error. And, of course, your float value of -1.369258488045704E+15 is much less than that range.

Since you say you only want two decimals, try converting it to decimal(38, 2). That should fix your problem.

Tom

Answer 2

My guess is that the type of the column you are converting is not a float, but in fact is a character string. If that is true, then you must convert it to float and then to decimal, for example

Declare @Sample Table(FloatColumn varchar(225));
Insert @Sample(FloatColumn) Values
('0'),
('-1.369258488045704E+15');

Select FloatColumn, Cast(Cast(FloatColumn As float) As decimal(35,17)) As DecimalColumn
From @Sample;

If that is not what is going on, please give us some sample data in the form of Create Table or Declare Table and Insert Statements like I did above. Also if you are receiving an error message give us the exact error message.

Tom

Answer 3

some values in the column is scientific numbers (example below).

If the source is off type float then it is a numerice value without any format; so is it really float or a string?
What type is the source, what's the destination, how do you try to import and what is the issue (or error) here?

Answer 4

Hi Olaf!

I use Linked server to import financial transactions from a supplier who provides a financial solution to our Company. I use a stored procedure to import from Linked server. This is the value field in the transaction table so it should be a numeric value. According to our supplier the datatype of the field should be money but it seems a little strange with all these decimals. I start with importing to a Staging table and then to may Fact Table. My problem started when i try to import Kpi values which sometimes seems to hold very long decimals values. I have tried to import to my Staging table with a value field of decimal(38,30) which is quiet much decimals but i get Error message "Msg 8115, Level 16, State 6.
Arithmetic overflow error converting float to data type numeric." When i adjust the datatype in my staging table to Float it works fine to import the values. But then i would like to Cast/Convert the float value to numeric/decimal when i transfer the transactions from Staging to Facttable with something like two decimals. But when i try that with round with two decimals i get the arithmetic overFlow error message. So i hope someone have a solution to this probolem.

Answer 5

Arithmetic overflow error converting float to data type numeric."

You get the error if the source data is ""larger" then the destination column by it's definition, e.g. this raises the same error. You have to check size of source and destination.

select CONVERT(decimal(8, 2), 123456789.123456789)