Menu
I have a view which needs to return type decimal for columns stored as float.
I can cast each column to decimal as follows:
, CAST(Field1 as decimal) Field1
The problem with this approach, is that decimal defaults to 18,0, which automatically rounds the float columns to 0. I would like to keep a precision of up to 12 decimal places.
However, if I do this:
, CAST(Field1 as decimal(12,12)) Field1
I get a runtime error:
"Arithmetic overflow error converting float to data type numeric"
the float column is defined as length: 8 Precision: 53 in the table. I can not modify anything about the table.
What's the proper way to cast it as decimal w/out losing decimal precision?
893
The rules for converting a floating point number into decimal are simply to reverse of the decimal to floating point conversion: If the original number is in hex, convert it to binary. Separate into the sign, exponent, and mantissa fields. Extract the mantissa from the mantissa field, and restore the leading one.
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
The Basic syntax of Decimal data type in SQL Server It is denoted as below: decimal [(p [,s])]
12, 12 means no digits before the decimal separator: 12 digits in total, 12 of them being after the period.
Use a more appropriate range, say:
DECLARE @var FLOAT = 100
SELECT CAST(@var as decimal(20,12))
which gives you 8 digits before the separator, or adjust it as needed.
197
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
