CODEWITHSUNDEEP
sqlfloating-pointprecisiontruncate
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

SQL set floating point precision

For a SQL int that is being converted to a float, how do I set the precision of the floating point number?

This is the selection I would like to truncate to two or 3 decimal places:

AVG(Cast(e.employee_level as Float))avg_level,

Thanks!

Upvotes: 15

Views: 101017

Answers (3)

C J
C J

Reputation: 173

I have had the same issue when calculating a percentage and needing a resulting string value.

Example: 68 is what % of 379

Result is a float = 17.9419525065900

You can cast/convert to Numeric with the Precision = 2 and get 17.94

If you need the value as a string you can then cast it as a VarChar if needed.

You can use Round() as well but in this case it only makes 17.9419525065900 = 17.9400000000000.

You can also use Ceiling() and Floor() to get the next highest or lowest integer.

Ceiling(17.9419525065900) = 18

Floor(17.9419525065900) = 17

Using these combinations you should be able to achieve a result in any format you need.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95592

As a general rule, you can't specify the number of digits after the decimal point for a floating-point number. Floating point data types store the closest floating-point approximation to any given value. The closest floating-point approximation is unlikely to have the number of digits you want. Although you might be able to suppress every digit after the third one, that will only change the appearance of the value, not the value itself.

Integers are a different story. An integer--stored, converted, or cast to a floating-point data type--will be stored exactly over a large range. Floating-point data types don't have to store any fractional units for integers.

I'd suggest, though that the best practice for you is to

  • avoid casting integers to floating-point if you don't need fractional units, or
  • cast integers to decimal or numeric if you do need fractional units, or
  • handle display issues entirely in application code.

Upvotes: 4

Bort
Bort

Reputation: 7618

In TSQL, you can specify two different sizes for float, 24 or 53. This will set the precision to 7 or 15 digits respectively.

If all you want to do is truncate to a set number of decimal places, you can use ROUND, ie:

ROUND(AVG(CAST(e.employee_level as float)), 3)

Upvotes: 28

Related Questions