# my sql queries generate correct figures.... then random jumble after 2nd decimal place. **URL:** \ **Category:** Factual Questions\ **Created:** [June 23, 2009, 6:16pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745 "2009-06-23T18:16:52Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 23, 2009, 6:16pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/1 "2009-06-23T18:16:52Z")
The always happens. I’m dealling with a crappy database that stores numbers as text, so I use the cast function… sum(cast(total as real)) but I sometimes get numbers like this… 7891.90000110865 where the figure of 7891.90 is correct. The actual data is never more than two decimal places in precision, but I always get these weird totals. Should I be using something other than cast? ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [June 23, 2009, 6:34pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/2 "2009-06-23T18:34:07Z")
> [@Lobsang](#): > > The always happens. I’m dealling with a crappy database that stores numbers as text, so I use the cast function… > > sum(cast(total as real)) > > but I sometimes get numbers like this… > > 7891.90000110865 > > where the figure of 7891.90 is correct. The actual data is never more than two decimal places in precision, but I always get these weird totals. > > Should I be using something other than cast? Weird. It seems a bit like floating point innacuracy, but that wouldn’t give you quite so many decimal places of garbage on the right side compared to the max precision of real. In any event, can you round to 2 decimal places, either in SQL or in your client language? ---
**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\ **Post date:** [June 23, 2009, 6:40pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/3 "2009-06-23T18:40:02Z")
I’m guessing you are on sql server, which has a few quirks I am not used to, but does the construct “sum(cast(total as real(10,2)))” work in it. Where the (10,2) tells it you want ten digit field with two spaces after the decimal, ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 23, 2009, 6:48pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/4 "2009-06-23T18:48:23Z")
Doesn’t accept. But after a bit of goggling I’ve replaced real with float in the cast and the problem goes away. But I’m still curious to know why it happens 🙂 eta: In fact, to make it even more of a weirdness, not only is the data never more precise than two decimal places, but everything is in multiples of .10 so effectively just one decimal place of precision for the actual data. So with the data I’m giving I should only ever see a 10 or a 20 or a 30 (and so on) after the decimal place. ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [June 23, 2009, 7:15pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/5 "2009-06-23T19:15:02Z")
I think there’ are two anomalies that combine together to create this problem. First, from: [float and real (Transact-SQL) - SQL Server | Microsoft Learn](http://msdn.microsoft.com/en-us/library/ms173773.aspx) “Approximate-number data types for use with floating point numeric data. Floating point data is approximate; **therefore, not all values in the data type range can be represented exactly.** ” Second, In SQL Server the ISO synonym for REAL = is FLOAT(24), whereas the default for FLOAT without specifying a precision is FLOAT(53). My speculation is that the extra precision of the default FLOAT is such that you don’t see the weird junk at the end. ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [June 23, 2009, 7:25pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/6 "2009-06-23T19:25:52Z")
> [@Lobsang](#): > > The always happens. I’m dealling with a crappy database that stores numbers as text, so I use the cast function… Out of curiosity, when you say the data is stored as text; are you saying that the data really is a data type of TEXT? Or could you be saying that the data type is CHAR or VARCHAR? I don’t think it matters, but I’d certainly check that out in addition to my previous post to see if it matters. ---
**Author:** ![LilShieste](https://avatars.discourse-cdn.com/v4/letter/l/9f8e36/32.png) [@LilShieste](https://boards.straightdope.com/u/LilShieste)\ **Post date:** [June 23, 2009, 7:44pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/7 "2009-06-23T19:44:35Z")
This is definitely a floating point precision problem. As **Enright3** mentioned above, the reason you’re not seeing the junk decimals when you use float instead of real is due to the higher precision used by the float data type. However, you can’t still be 100% sure that the same thing won’t happen even with the change you made (using float); such is the world of floating points in computers. If you absolutely, _positively_, _must_ be sure that this doesn’t happen again, then you will have to use a different data type (e.g., [decimal](http://msdn.microsoft.com/en-us/library/ms187746.aspx)). But if you’re alright with your floating points being valid only 99.99999% of the time, you can just continue using float or real. IOW, if you’re a bank dealing with amounts in people’s bank accounts, approximate values won’t cut it. But if you’re dealing with the number of seconds it took to complete a transaction, approximate values are just fine (usually). ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 23, 2009, 8:11pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/8 "2009-06-23T20:11:33Z")
> [@Enright3](#): > > Out of curiosity, when you say the data is stored as text; are you saying that the data really is a data type of TEXT? Or could you be saying that the data type is CHAR or VARCHAR? > > I don’t think it matters, but I’d certainly check that out in addition to my previous post to see if it matters. char(10). FYI I’m dealing with bets. The minimum multiple of a bet currently allowed is ten cents, so all data is a multiple of 0.10. Other amounts can enter into the table (such as if someone withdraws the one dollar twelve cents they have in their account) but these are filtered out by the query. ---
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [June 23, 2009, 8:35pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/9 "2009-06-23T20:35:27Z")
Since you’re dealing with money, you need to be absolutely precise. Can you do basic arithmetic on the figures? A simple method would be to multiply by 100, take the integer, then divide by 100. ---
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [June 23, 2009, 8:37pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/10 "2009-06-23T20:37:53Z")
Even better might be to use BCD ---
**Author:** ![Armilla](https://avatars.discourse-cdn.com/v4/letter/a/e19b73/32.png) [@Armilla](https://boards.straightdope.com/u/Armilla)\ **Post date:** [June 24, 2009, 12:45am UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/11 "2009-06-24T00:45:27Z")
I’m assuming a MSSQL server basis here, but it applies equally to Sybase ASE and some other databases: Float and real are approximate datatypes and, if you’re thrashing through a large number of calculations, will tend to give you this kind of rounding error. If you’re dealing with financial transactions I’d recommend the money datatype, which always floats at 4 decimal places, which should be sufficient for your needs. If you need greater precision go with a tailored decimal type with a specified precision. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [June 24, 2009, 8:45am UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/12 "2009-06-24T08:45:47Z")
Definitely a floating-point precision error. Take a look at the list of SQL Server data types here: [Data types (Transact-SQL) - SQL Server | Microsoft Learn](http://msdn.microsoft.com/en-us/library/ms187752.aspx). You want to be using one of the “Exact numeric” type rather than the “approximate numeric” type, to stop this from happening. Either money or decimal would appear to be appropriate. Both float and real are approximate numerics. ---
**Author:** ![crazyjoe](https://avatars.discourse-cdn.com/v4/letter/c/f14d63/32.png) [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)\ **Post date:** [June 24, 2009, 1:58pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/13 "2009-06-24T13:58:44Z")
Agreed…I am not sure where the idea came from to use Float or Real in the first place. This is what the decimal data type was made for. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 26, 2009, 4:06pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/14 "2009-06-26T16:06:49Z")
Are you saying use the money data type when casting? or change the field to money? I’d love to change the field to money, but it’s a massive table and I think I’ll live with it being char(10) for now. So far the ‘problem’ hasn’t been a problem. ---
**Author:** ![LilShieste](https://avatars.discourse-cdn.com/v4/letter/l/9f8e36/32.png) [@LilShieste](https://boards.straightdope.com/u/LilShieste)\ **Post date:** [June 26, 2009, 4:21pm UTC](https://boards.straightdope.com/t/my-sql-queries-generate-correct-figures-then-random-jumble-after-2nd-decimal-place/500745/15 "2009-06-26T16:21:06Z")
> [@Lobsang](#): > > Are you saying use the money data type when casting? or change the field to money? > > I’d love to change the field to money, but it’s a massive table and I think I’ll live with it being char(10) for now. So far the ‘problem’ hasn’t been a problem. Optimally, the field itself should have a data type of money (unless some rows contain non-monetary values). However, if you don’t want to go that route, casting the text field as a money or smallmoney data type would solve your immediate problem, and ensure you never see it again. (Casting it as float doesn’t come with that guarantee.)