在SQL Server 2008中将float转换为十进制 [英] Converting float to decimal in SQL Server 2008

问题描述

我有一个视图，该视图需要为存储为float的列返回类型十进制。

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

此方法的问题在于，小数默认为18,0 ，该值会自动将float列舍入为0。我想保持最多12位小数的精度。

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.

但是，如果我这样做：

, CAST(Field1 as decimal(12,12)) Field1

我遇到运行时错误：

"Arithmetic overflow error converting float to data type numeric"

浮点数列在表中定义为长度：8精度：53 。我无法修改有关该表的任何内容。

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?

推荐答案

12，12 表示小数点分隔符前没有数字：<总共code> 12 个数字，其中 12 个在此期间之后。

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))

这使您 8 分隔符前的数字，或根据需要进行调整。

which gives you 8 digits before the separator, or adjust it as needed.

这篇关于在SQL Server 2008中将float转换为十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！