In the following query
在以下查询中
declare @a float(23)declare @b float(23)declare @c float(53)set @a = 123456789012.1234set @b = 1234567.12345678set @c = @a * @bselect @cselect LTRIM(STR((@c),32,12))declare @x decimal(16,4)declare @y decimal(16,8)declare @z decimal (32,12)set @x = 123456789012.1234set @y = 1234567.12345678set @z = @x * @yselect @zI get answers as
我得到了答案
1.52415693411713E+17152415693411713020.000000000000152415692881907790.143935926652From the above answers the third answer is the correct one. Is this the reason why float data type is called Approximate Numeric Data Type
从上面的答案来看,第三个答案是正确答案。这就是浮点数据类型被称为近似数值数据类型的原因
Or am I doing something fundamentally wrong.
或者我做了一些根本错误的事情。
BTW this is due to a problem I have with legacy system wherein I have to use float as storage data type, at the same time in there should not be loss of precision while calculation.
顺便说一句,这是由于遗留系统存在的一个问题,我必须使用float作为存储数据类型,同时在计算时不应该丢失精度。
Please suggest alternatives, or an explanation.
请提出替代方案或解释。
2 个解决方案
#1
6
Float is accurate to 15 significant figures only (in SQL Server).
Float仅精确到15位有效数字(在SQL Server中)。
This is demonstrated by 1.52415693411713 E+17 where 1.52415693411713 (15 digits) is as accurate as you'll get. The final 020... after 152415693411713 with STR is
made up
is the resolution of floating point
这可以通过1.52415693411713 E + 17来证明,其中1.52415693411713(15位数)与您获得的一样准确。最后的020 ......在152415693411713之后用STR组成的是浮点的分辨率
To keep precision, don't use float. It is that simple. CAST to decimal if you want for calculation, but if you CAST back to float you are limited to 15 digits
为了保持精度,请不要使用浮点数。就这么简单。如果要计算,则CAST为十进制,但如果CAST返回浮动,则限制为15位数
See "What Every Computer Scientist Should Know About Floating-Point Arithmetic"
请参阅“每个计算机科学家应该知道的关于浮点算术的内容”
#2
0
The last answer
最后的答案
152415692881907790.143935926652is providing scale up to 12 decimal places because you have declared @z accordingly.
因为你已经相应地声明了@z,所以提供最多12位小数的比例。
declare @z decimal (32,12)The second parameter in this declaration is scale which is set to 12.
此声明中的第二个参数是scale,它设置为12。
More on the this can be found at http://msdn.microsoft.com/en-us/library/ms187746.aspx
有关这方面的更多信息,请访问http://msdn.microsoft.com/en-us/library/ms187746.aspx
#1
6
Float is accurate to 15 significant figures only (in SQL Server).
Float仅精确到15位有效数字(在SQL Server中)。
This is demonstrated by 1.52415693411713 E+17 where 1.52415693411713 (15 digits) is as accurate as you'll get. The final 020... after 152415693411713 with STR is
made up
is the resolution of floating point
这可以通过1.52415693411713 E + 17来证明,其中1.52415693411713(15位数)与您获得的一样准确。最后的020 ......在152415693411713之后用STR组成的是浮点的分辨率
To keep precision, don't use float. It is that simple. CAST to decimal if you want for calculation, but if you CAST back to float you are limited to 15 digits
为了保持精度,请不要使用浮点数。就这么简单。如果要计算,则CAST为十进制,但如果CAST返回浮动,则限制为15位数
See "What Every Computer Scientist Should Know About Floating-Point Arithmetic"
请参阅“每个计算机科学家应该知道的关于浮点算术的内容”
#2
0
The last answer
最后的答案
152415692881907790.143935926652is providing scale up to 12 decimal places because you have declared @z accordingly.
因为你已经相应地声明了@z,所以提供最多12位小数的比例。
declare @z decimal (32,12)The second parameter in this declaration is scale which is set to 12.
此声明中的第二个参数是scale,它设置为12。
More on the this can be found at http://msdn.microsoft.com/en-us/library/ms187746.aspx
有关这方面的更多信息,请访问http://msdn.microsoft.com/en-us/library/ms187746.aspx