0

I want to change float columns to decimal columns, so I use max(x) to determine the numbers of digits I need before the decimal, but how can I check the length after the decimal?

For example:

float number: 300,9886722

How can I see that they are 7 digits behind the ,?

Thanks for your help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Search Bug and Destroy
  • 1
  • 5
  • 2
    You can't. You need to figure this out based on business requirements. Floats go on and on, indefinitely in most cases. – Gordon Linoff Apr 16 '20 at 12:21
  • @GordonLinoff: No floating-point value in a MSSQL `float` goes on “indefinitely.” It is a fixed-width binary-based format and each represented value can be represented with a finite number of digits in either binary or decimal. – Eric Postpischil Apr 16 '20 at 12:25
  • The number of digits you need after the decimal point depends on your requirements. If you need to distinguish numbers up to a certain resolution, you need whatever number of digits achieves that resolution. If you need to exactly represent the original number currently stored in the `float` field, you may need several hundred digits, to capture subnormal numbers in that format. If you know the range of non-zero numbers in your database(s), you may be able to compute a lower bound than that. – Eric Postpischil Apr 16 '20 at 12:27
  • In the format MSSQL apparently uses for the 8-byte floating-point type, you need 17 significant digits in order to guarantee enough information to uniquely distinguish the original number. But that is 17 digits starting with the first significant (non-zero) digit. So how many you need after the decimal point depends on where it starts after the decimal point—if your numbers are all .1 or greater, than 17 digits after the decimal point suffices. If they are all .001 or greater, then 19 suffices, and so on. And this only captures information to reconstruct the original, not the actual original. – Eric Postpischil Apr 16 '20 at 12:31
  • I thought, maybe we could Select max(len)(varchar)(x) from y something like this but with the right syntax, so first cast to a string, than determine the length and get the max from it, to get only the digits after the "," maybe before %1. – Search Bug and Destroy Apr 16 '20 at 12:41
  • Converting to a varchar won't give you the same results. Take this for example (sorry, line breaks don't show up): DECLARE @f FLOAT SET @f = PI() DECLARE @v VarChar(1000) SET @v = CONVERT(VarChar(1000), @f ) SELECT @v SELECT @f – InbetweenWeekends Apr 16 '20 at 13:16
  • To understand the problem here, you should understand the format and representation of a floating-point number. A binary-based floating-point number does not “have” decimal digits. Fundamentally, every binary floating-point number is some integer n multiplied by 2 to some integer power e, so n•2^e. In the IEEE-754 format, the representable value that is nearest to 300.9886722 is 2647524359302071•2^−43, which is 300.9886721999999963372829370200634002685546875. Many programming languages or software applications do not show you all those digits by default… – Eric Postpischil Apr 16 '20 at 13:46
  • … When they present a floating-point number, they abbreviate. So converting to a string or decimal representation will not necessarily get you all the decimal digits you need to exactly represent the original value. And, even if you do get all those digits, you do not know what original number they came from. Did the number that got recorded in the database come from 300.9886722, or did it come from 300.9886721999? There is no way of knowing once the number was rounded into a binary floating-point format. How many digits you need depends on what your needs are. – Eric Postpischil Apr 16 '20 at 13:48

0 Answers0