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.
这种方法的问题是，十进制默认为18,0，它自动将浮点列四舍五入为0。我希望保持精确到小数点后12位。
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.
浮动柱在表中定义为长度:8精度:53精度。我不能修改表的任何内容。
What's the proper way to cast it as decimal w/out losing decimal precision?
把它转换成十进制w/输出精度损失的正确方法是什么?
1 个解决方案
#1
21
12, 12 means no digits before the decimal separator: 12 digits in total, 12 of them being after the period.
12表示小数分隔符前没有数字:总共12位，其中12位在句点后。
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.
在分隔符之前给你8位数字，或者根据需要进行调整。
#1
21
12, 12 means no digits before the decimal separator: 12 digits in total, 12 of them being after the period.
12表示小数分隔符前没有数字:总共12位，其中12位在句点后。
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.
在分隔符之前给你8位数字，或者根据需要进行调整。