11

I need to convert a Float to Decimal(28,10) in SQL Server. My problem is that due to the nature of float, and the way conversions are done, simply casting the float may make it appear to be the wrong number to my users.

For example:

Float:               280712929.22 
Cast as Decimal:     280712929.2200000300
What I think I want: 280712929.2200000000

I understand a bit about the way float works (that it's an approximate data type etc.), but admittedly not enough to understand why it adds the 300 at the end. Is it simply garbage as a side effect of the conversion, or is it somehow a more accurate representation of what the float actually stores? To me it looks like it's pulled precision out of thin air.

Ultimately, I need it be accurate, but also to look "right." I think I need to get that bottom number, as then it looks like I've just added trailing zeroes. Is this possible? Is this a good or bad idea, and why? Other suggestions are welcome.

Some other examples:

Float:           364322379.5731
Cast as Decimal: 364322379.5730999700
What I want:     364322379.5731000000

Float:           10482308902
Cast as Decimal: 10482308901.9999640000
What I want:     10482308902.0000000000

Side note: the new database table that I'm putting these values into is readable by my user. They actually only need two decimal places right now, but that might change in the future so we've decided to go with Decimal(28,10). The long term goal is to convert the float columns that I'm getting my data from to decimal as well.

EDIT: Sometimes the floats that I have have more decimal places than I'll ever need, for example: -0.628475064730907. In this situation the cast to -0.6284750647 is just fine. I basically need my result to add zeroes on to the end of the float until I have 10 decimal places.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
C Walker
  • 793
  • 2
  • 9
  • 19
  • Maybe I found the answer. http://stackoverflow.com/questions/34316871/how-can-the-following-result-of-casting-from-float-to-numeric-be-explained – Dmitriy Dokshin Dec 16 '15 at 19:48

3 Answers3

9

You need to cast once to round, and once to add decimal places back (there are other ways too, surely, using STR, ROUND etc.):

DECLARE @c TABLE(x FLOAT);

INSERT @c SELECT 280712929.22;
INSERT @c SELECT 364322379.5731;
INSERT @c SELECT 10482308902;

SELECT x, 
    d = CONVERT(DECIMAL(28,10), x), 
    rd = CONVERT(DECIMAL(28,10), CONVERT(DECIMAL(28,4), x))
FROM @c;

Results:

x               d                       rd
--------------  ----------------------  ----------------------
280712929.22    280712929.2200000300    280712929.2200000000
364322379.5731  364322379.5730999700    364322379.5731000000
10482308902     10482308902.0000000000  10482308902.0000000000

If you want it to be accurate and look right, stop using FLOAT, which is an approximate data type and defies logic for most people outside of a strict math background. Use DECIMAL with a larger scale than you need, and format it to the number of decimal places you need now (in your queries, or create a view, or create a computed column). If you store more info than you need now, you can always expose more later. You can also choose to not give users direct access to your table.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • the number of decimal numbers may differ, at least that's what I think. – aF. Jun 20 '12 at 12:20
  • That does help to a degree. However, that means that I will be truncating digits off of floats where I actually have more than 4 decimal places in the first place, ie: 517822.18109027. Will edit post to clarify. – C Walker Jun 20 '12 at 12:32
  • 2
    If you need to conditionally decide how many decimal places to show on every row, based on the value in that row, I think you're crossing over into string parsing/formatting territory. This is probably best done at the presentation layer, not in the database. – Aaron Bertrand Jun 20 '12 at 12:35
  • It still shows the same amount of decimal places (10) on every row, it's just that the number of decimal places that I start with in the float varies, so I can't arbitrarily truncate it and start adding 0's at the end. – C Walker Jun 20 '12 at 12:41
  • 2
    Then I still suggest the formatting is better done in your client language rather than in the query. SQL's strength is not string parsing, which is what this amounts to. – Aaron Bertrand Jun 20 '12 at 12:46
3

I'm not sure if this falls under necroposting, but I had a similar problem recently so I thought I might post.

This may be ugly as sin, but seemed to work (modified from Aaron's response above).

DECLARE @myTable TABLE(x FLOAT);

INSERT INTO @myTable VALUES
   (280712929.22),
   (364322379.5731),
   (10482308902),   
   (-0.628475064730907);

SELECT x, 
       d = CONVERT(DECIMAL(28,10), x),                 
       NewDec = CONVERT(DECIMAL(28,10),           
                        CONVERT(DECIMAL(16,15), 
                                LEFT(CONVERT(VARCHAR(50), x, 2),17)) 
                        * POWER(CONVERT(DECIMAL(38,19),10),  
                                RIGHT(CONVERT(varchar(50), x,2),4)))                       
FROM @myTable;

Results:

x                   d                       NewDec
------------------  ----------------------  ----------------------
280712929.22        280712929.2200000300    280712929.2200000000
364322379.5731      364322379.5731000300    364322379.5731000000
10482308902         10482308902.0000000000  10482308902.0000000000
-0.628475064730907  -0.6284750647           -0.6284750647
Dan
  • 41
  • 2
1

I came to this problem when I was needed to convert big data table from float to decimal(28,15). As specify in comment of the @Dan answer, his answer was not working as expected for some values.

Here is my final version used to make the update

DECLARE @myTable TABLE(x FLOAT);

INSERT INTO @myTable VALUES
(280712929.22),
(364322379.5731),
(10482308902),   
(-0.628475064730907),   
(-0.62847506473090752665448522),
(8.828),
(8.9),
(8.999),
(8),
(9),
(0.000222060864421707),
(5.43472210425371E-323),
(1.73328282953587E+81);

SELECT x, 
   d = CONVERT(DECIMAL(28,15), ROUND( CONVERT(DECIMAL(28,15),           
                CONVERT(DECIMAL(16,15), 
                        LEFT(CONVERT(VARCHAR(50), x, 2),17)) 
                * POWER(CONVERT(DECIMAL(38,19),10),  
                         CASE   
  WHEN RIGHT(CONVERT(varchar(50), x,2),4) > 12 THEN 12
  ELSE RIGHT(CONVERT(varchar(50), x,2),4)
  END)), 14 )),

 SimpleVarchar = TRY_CONVERT(VARCHAR(50), x),
 AnsiVarchar = TRY_CONVERT(VARCHAR(50), x, 2)
 FROM @myTable;

I loosed a very small part of precision because I rounded on the decimal 14. But it's acceptable in my case.

Jerome2606
  • 933
  • 20
  • 42