WHy do I get extra decimal places for float?

Question

I create a table like this:

CREATE TABLE [dbo].[tblBar1M](
    [utcDT] [datetime2](7) NOT NULL,
    [Ticker] [text] NOT NULL,
    [FwdAdjMultiplier] [float] NULL,
    [FwdAdjOpen] [float] NULL,
    [FwdAdjHigh] [float] NULL,
    [FwdAdjLow] [float] NULL,
    [FwdAdjClose] [float] NULL,
    [ActualClose] [float] NULL,
    [ActualLastBid] [float] NULL,
    [ActualLastAsk] [float] NULL,
    [Volume] [float] NULL,
    [Seq] [float] NOT NULL,
 CONSTRAINT [PK_tblBar1M] PRIMARY KEY CLUSTERED 
(
    [Seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I create insert command like:

    commandText = "INSERT INTO BT..tblBar1M VALUES(@utcDT,@Ticker,@FwdAdjMultiplier,@FwdAdjOpen,@FwdAdjHigh,@FwdAdjLow,@FwdAdjClose,@ActualClose,@ActualLastBid,@ActualLastAsk,@Volume,@Seq)";

    cmdInsertBar1M = new SqlCommand(commandText, conn);
    cmdInsertBar1M.Parameters.Add("@utcDT", SqlDbType.DateTime2);
    cmdInsertBar1M.Parameters.Add("@Ticker", SqlDbType.NVarChar);
    cmdInsertBar1M.Parameters.Add("@Seq", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@FwdAdjMultiplier", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@FwdAdjOpen", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@FwdAdjHigh", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@FwdAdjLow", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@FwdAdjClose", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@ActualClose", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@ActualLastBid", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@ActualLastAsk", SqlDbType.Float);
    cmdInsertBar1M.Parameters.Add("@Volume", SqlDbType.Float);

And I fill parameters:

 double FwdAdjMultiplier = 1.25;
double baropenMid = 0.727735

cmdInsertBar1M.Parameters["@FwdAdjOpen"].Value = (float)Math.Round( baropenMid * FwdAdjMultiplier,6);

float f = (float)Math.Round(baropenMid * FwdAdjMultiplier, 6);
//f = 909.668762

As you can see in comment about from VS Inspection f has 6 DP. BUT after insert in table in SSMS, FwdAdjOpen (surrounded by ***) has many more DP.

WHY?

utcDT   Ticker  FwdAdjMultiplier    FwdAdjOpen  FwdAdjHigh  FwdAdjLow   FwdAdjClose ActualClose ActualLastBid   ActualLastAsk   Volume  Seq
2016-04-28 14:23:00.5885822 GL  1.25    ***909.668762207031***  909.825012207031    904.681274414063    906.150024414063    724.919982910156    724.640014648438    725.200012207031    509600  6

Where are you seeing the erroneous data? In SQL Server Management Studio? — Michael Liu, Apr 28 '16 at 14:43
The question is already answered, but FYI, your values for `baropenMid` and `FwdAdjMultiplier` do not multiply to the value you give. `f` in your code gives `0.909669` — Quantic, Apr 28 '16 at 14:53

score 4 · Accepted Answer · answered Apr 28 '16 at 14:45

4

T-SQL FLOAT is double precision. C# float is single precision. What C# calls float is REAL in T-SQL. What you're getting is effectively (double) 909.668762f.

Either use double in C# if you want the extra precision, or REAL in SQL Server if you don't. See Books Online.

answered Apr 28 '16 at 14:45

Jeroen Mostert

27,176
2
52
85

1

`Console.WriteLine((double)909.668762f);` does indeed print the value given in OP's question, `909.668762207031`. – Quantic Apr 28 '16 at 14:51
out of curiousity, why the downvote? It seems to be correct and answers the question. – user1666620 Apr 28 '16 at 14:53

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

Float has inherently precission error, you can't represent a concrete value (unless it fits exactly on the float representable values), so, even if you Round the float, it's undelying value will be different, because of that the final value on the database has more decimals.

This can give you more details about precission errors on floats: Floating point inaccuracy examples

edited May 23 '17 at 10:33

Community

1
1

answered Apr 28 '16 at 14:45

Gusman

14,905
2
34
50

WHy do I get extra decimal places for float?

2 Answers2