0

I have a FLOAT column in a SQL Server database that appears as follows in SQL Server Management Studio.

18.001

When I read that value into a float variable, and format it using sprintf() ("%f"), it appears as:

18.000999

When I read that value into a double variable, and format it using sprintf(), it appears as:

18.001000

Could I get some suggestions on this? The values being stored are generally under 100, with up to 3 decimal places. What is the best SQL Server type? What is the best C++ type? And should I be using some rounding technique to get it in the format I want?

Note: I'm not actually using sprintf(), I'm using CString.Format(), but the expected behavior is the same.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • If you're concerned about precision, C++ got float < double < long double. – nada Sep 16 '19 at 17:13
  • @nada: Right, but that seems a bit of overkill for a value that is less than 100. – Jonathan Wood Sep 16 '19 at 17:15
  • 1
    @JonathanWood if you care about precision, it is not. – SergeyA Sep 16 '19 at 17:17
  • @SergeyA: Why would I need a `long double` for 3 decimal places? Seems really more of a formatting issue. – Jonathan Wood Sep 16 '19 at 17:19
  • 1
    @JonathanWood the number is not representable exactly in floating point. The more precision you have, the closer you get to your *actual* number. – SergeyA Sep 16 '19 at 17:21
  • @SergeyA: I get that. But I can round to 3 decimal places without any more precision. For that reason, more precision seems like overkill here. – Jonathan Wood Sep 16 '19 at 17:23
  • I am not understanding your problem. If you simply want to always round up to 3 decimal places, than just round up? – SergeyA Sep 16 '19 at 17:25
  • There is no best c++ datatype. These inaccuracies are to be expected as explained in the referenced duplicate. – Tab Alleman Sep 16 '19 at 17:30
  • Yes I did, and many others like it. The point of the flagged duplicate, if you read the answers, is that float datatypes are not precise and you should expect inconsistencies from language to language. – Tab Alleman Sep 16 '19 at 17:35
  • If it is stated somewhere in your question that you already understand about float precision, I am still missing it on re-read. To me it reads as though you are mystified by the inconsistencies. If you edit your question to be more focused on how best to work around these known inconsistencies, I'll vote to re-open. – Tab Alleman Sep 16 '19 at 17:40

3 Answers3

1

The values being stored are generally under 100, with up to 3 decimal places.

SQL databases support the numeric/decimal (the two are synonyms) types for fixed-point values. For your specific type, you could use decimal(6, 3). That is six significant digits, with three of them to the right of the decimal point. These two values are called scale and precision respectively.

If the values can differ a bit from this, you might want a wider range.

With decimal/numeric, what-you-see-is-what-you-get. I would recommend storing them in the database as fixed-point numbers.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Do you know the best C variable to hold such a value? – Jonathan Wood Sep 16 '19 at 17:05
  • 2
    The biggest question would be on the proper C++ data type for it. – SergeyA Sep 16 '19 at 17:11
  • 1
    @SergeyA I would probably scale the value up to be an integer in my select statement or create a view. That way you could store the value in a normal C++ int and enjoy all the performance and convenience benefits of a usual primitive type. – eike Sep 16 '19 at 18:15
1

Answering the question on it's face value, assuming floating point should be used and fixed point is not applicable.

Unless you are really tight on memory, there is really no reason to use anything for floating numbers in C++ but double. Float looses precision without giving you much in return. You can also try long double, but in my experience it is rather overkill. Also, if your compiler is MSVC, I have heard it's long doubles are the same as doubles.

SergeyA
  • 61,605
  • 5
  • 78
  • 137
-1

In alternative to the fixed comma decimals proposed already, just use ordinary integers!

Instead of storing 18.001 seconds, you'd store 18001 milliseconds, you wouldn't store Euro, Pound, Dollar, but tenth of a cent or penny, ...

Type in C++ would be an integer as well, large enough to hold maximum numbers you need, e. g. uint32_t, int64_t, ...

Aconcagua
  • 24,880
  • 4
  • 34
  • 59
  • I'm doing that for my currency values. However, I have some generic floating point column classes. I don't really want to tie down the number of decimal places to a fixed number. – Jonathan Wood Sep 16 '19 at 17:10
  • Assuming IEEE754, floating point is not really suitable to represent currency values. Be aware that even a most simple value like 0.2 is periodic in binary, so cannot be represented exactly, no matter if you consider float or double. Fixed-comma arithmetics with integers are always precise, with exception, though, if e. g. needing to calculate interests. You will need some rounding then (e. g. `value = (value * 107 + 50) / 100`), but you can always have that at only those very specific points where you really need it (and can easily select rounding strategy as desired/required). – Aconcagua Sep 16 '19 at 19:03
  • If you still insist on floating point, then I'd opt for the highest precision available, i. e. double or possibly even long double. – Aconcagua Sep 16 '19 at 19:03