Convert Float to Decimal (SQL Server)

Question

I need to convert Float to Decimal (28,10) in SQL Server. My problem is that due to the nature of the float and the way it is converted, just using float may make it wrong for my users.

For instance:

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

I am a little versed in how the float works (this is an example data type, etc.), but admittedly is not enough to understand why it adds 300 at the end. Is it just rubbish as a side effect of the conversion, or is it somehow a more accurate idea of ​​what the float actually stores? It seems to me that he pulled accuracy out of thin air.

Ultimately, I need it to be accurate, but also look "right." I think I need to get this lower number, since then it looks like I just added trailing zeros. Is it 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 in which I put these values ​​is being read by my user. In fact, they only need two decimal places right now, but that may change in the future, so we decided to go with Decimal (28.10). The long-term goal is to convert the float columns into which I get my data from decimal.

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

Answer 1

You need to give it once a round, and add decimal places once back (there are other ways, of course, 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:

 xd 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 not amenable to logic for most people who do not have a strong mathematical 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, either create a view or create a computed column). If you store more information than you need now, you can always open it later. You may also not give users direct access to your spreadsheet.

Answer 2

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

It may be ugly as a sin, but it seems to work (modified from Aaron's answer 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:

 xd NewDec ------------------ ---------------------- ---------------------- 280712929.22 280712929.2200000300 280712929.2200000000 364322379.5731 364322379.5731000300 364322379.5731000000 10482308902 10482308902.0000000000 10482308902.0000000000 -0.628475064730907 -0.6284750647 -0.6284750647