select ceiling(1285.0992 / 1.94712) result for the query above is 660 but the query below gives 661 as a result. declare @var1 float = 1285.0992 declare @var2 float = 1.94712 select ceiling(@var1 / @var2) can anyone tell me how that is?

First floating point arithmetic is approximate. In the first example, the literal 1285.0992 / 1.94712 does not specify a precision and probably uses a different precision (7 digits) rather than the float runtime operation (15 digits). try: select ceiling(1285.0992 / 1.94712), ceiling((cast(1285.0992 as float(53)) / cast(1.94712 as float(53))))

Is there any background round operation for floats?

Accepted answer

Bruce (SqlWork.com) 66,056 Reputation points

2024-09-04T15:14:42.3333333+00:00
First floating point arithmetic is approximate. In the first example, the literal 1285.0992 / 1.94712 does not specify a precision and probably uses a different precision (7 digits) rather than the float runtime operation (15 digits).

try:

select ceiling(1285.0992 / 1.94712), ceiling((cast(1285.0992 as float(53)) / cast(1.94712 as float(53))))
Please sign in to rate this answer.
Erland Sommarskog 112.6K Reputation points MVP

2024-09-04T21:14:29.47+00:00

In addition to what Bruce and Viorel say, have a look at this:

select 1285.0992 / 1.94712, cast(1285.0992 as float) / cast(1.94712 as float), 1285.0992 / 1.94712 -660, cast(1285.0992 as float) / cast(1.94712 as float) - 660

The output in SSMS:

As you can see, the division with float has a little remnant, which SSMS does not display when you look at the value from the division. But that is just because of the double-to-string library that SSMS uses.

If you instead use SQLCMD or Azure Data Studio, the display is different:

Yavuz RA 20 Reputation points

2024-09-11T21:48:36.2733333+00:00

Then we have a huge problem if it is approximate. How can we trust and use the built-in functions?

Erland Sommarskog 112.6K Reputation points MVP

2024-09-12T09:00:09.1566667+00:00

First of all, this is nothing that is unique to SQL Server, but this is something you will encounter in any environment that uses float.

I don't know what your values represent, but float values are intended for scientfic computation where full exactness are not needed because all values are typically measurements with a limited precision. "The thickness of this blade of metal is 1.23 mm +/- 0.005 mm". On the other hand, these values can be of very different magnitudes. In one end you have dimensions of the nuclear atom, in the other you have the distance from here to the Anedromda galaxy.

To be able to represent these in a computer, the float data type was introduced, and there are both 32- and 64-bit float values. They can cover very wide ranges, particularly the 64-bit values. But the price is that the decimal values can only represented approxamitely.

Far from all sorts of values are suitable to store in floats. For instance, financial values where you need precision down the last cent even for a value that has 9-10 digits before the decimal point can be problematic. You absolutely need to understand what you are doing and that the result can be a little wrong and be prepared to take corrective actions. Not the least if you apply functions like ceiling.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Is there any background round operation for floats?

0 additional answers

Your answer