How to convert real to float without adding extra digits

Question

I have table with real column, i need to multiple all row with billion , this give me good result(151112000):

declare @tbl table (n real)
insert into @tbl values(151.112)
select cast (n * 1000000 as float) AS [Quantity1]
from @tbl

But when i select from old existing table, 151.112 became to 151112048.

How it can be?

My old ddl table:

CREATE TABLE [dbo].[ProvidentSegmentPortfolioMonthlyHistory](
    [Fund_id] [int] NOT NULL,
    [Report_Date] [datetime] NOT NULL,
    [Segment_id] [int] NOT NULL,
    [Quantity] [real] NULL,
    [QuantityPercent] [real] NULL,
    [Date_Update] [datetime] NULL,
    [operator] [varchar](50) NULL,
 CONSTRAINT [PK_ProvidentSegmentPortfolioMonthlyHistory ] PRIMARY KEY CLUSTERED 
(
    [Fund_id] ASC,
    [Report_Date] ASC,
    [Segment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I'd say we need more than DDL, we need enough information to reproduce the issue. — Tab Alleman, Feb 13 '19 at 14:36
https://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples — Tab Alleman, Feb 13 '19 at 14:39
I understand that is floating point issue, but i don't understand why it is different from oldest table. — yantrab, Feb 13 '19 at 14:41
You showed a code sample that *does not* demonstrate the problem you're actually having. Give us a code sample that *does* demonstrate the problem you're actually having. — Aaron Bertrand, Feb 13 '19 at 14:45
Now we have some ddl. Can you share some data where this issue is repeatable? I can't reproduce what you claim is happening. — Sean Lange, Feb 13 '19 at 15:31
I also tried to reproduce it without success. i deal with existing table. — yantrab, Feb 13 '19 at 15:44

score 2 · Answer 1 · answered Feb 13 '19 at 14:48

2

I understand that is floating point issue, but i don't understand why it is different from oldest table.

It's different because you think the old table has a value of 151.112 in it, but it doesn't. It has a value of 151.112048 in it, which it displays as 151.112 because of floating data point inaccuracy.

How to resolve it is to use Decimal instead of Real in your table.

answered Feb 13 '19 at 14:48

Tab Alleman

31,483
7
36
52

You would need to give us enough information to reproduce the issue for me to comment further. – Tab Alleman Feb 13 '19 at 14:53
Can you give me example of how 151.112048 became to 151.112? – yantrab Feb 13 '19 at 14:53

How to convert real to float without adding extra digits

1 Answers1