1

What is the best data type in SQL Server 2008 R2 to store a value like 1.74269E-06?

I am trying to put as float, but the value always zero. Advice please.

TIA.

Regards,

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Haminteu
  • 1,292
  • 4
  • 23
  • 49
  • Try `decimal`. Is your value coming in the format in your sample or as the numerical value? – shree.pat18 May 07 '14 at 04:36
  • How many digits after the decimal point do you need to support? – Szymon May 07 '14 at 04:36
  • http://stackoverflow.com/questions/1209181/what-represents-a-double-in-sql-server – Dmitry Bychenko May 07 '14 at 04:37
  • `DECIMAL` or `FLOAT`. `FLOAT` if that exponent gets much bigger. What value are you putting in, how are you putting it in? Post a script that reproduces the issue. – Nick.Mc May 07 '14 at 04:38
  • Maybe this will help you: http://stackoverflow.com/questions/14276571/how-to-store-value-in-mssql-2008-with-high-precision – Aret May 07 '14 at 04:43
  • all, i am trying to create a storeprocedure. Inside, there's one variable like: SET Var2 = 0.049747 * (0.32931007 + 1.669994 * Var1 - (POWER(44.002253 * Var1, 2)) + (POWER(735.18335 * Var1, 3)) - (POWER(4749.7294 * Var1, 4))) .. If i calculate it on excel, the result should be, 1.74269E-06. Thats why im asking, what is the best data type for this kind of value? – Haminteu May 07 '14 at 06:51
  • Can you I suggest that you post that info first time around - we need some context. That's definitely a candidate for float' – Nick.Mc May 09 '14 at 04:41
  • Yes, before i am using some combination for each of my variable. decimal and float. but after i change all my variable to float. then now is good. – Haminteu May 09 '14 at 07:18

2 Answers2

2

When I insert that value into a datatype of float, it isn't zero.

You might be accidentally casting it to INT or displaying it incorrectly.

If your values are going to be very large or very small (and can't be held in DECIMAL) then you'll need to use FLOAT

DECLARE @Table TABLE (F FLOAT, D DECIMAL(19,7))

INSERT INTO @Table (F,D)
VALUES(1,1)

INSERT INTO @Table (F,D)
VALUES(1.74269E-06,1.74269E-06)

-- I can see the float here in exponential format
SELECT F,D FROM @Table

-- I can see it here in decimal format
SELECT CONVERT(DECIMAL(19,7),F),D FROM @Table

-- But if I cast to INT it disappears.
SELECT CONVERT(INT,F),D FROM @Table
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
1

Use decimal instead.

decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )] Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
  • I am trying to create a storeprocedure. Inside, there's one variable like: SET Var2 = 0.049747 * (0.32931007 + 1.669994 * Var1 - (POWER(44.002253 * Var1, 2)) + (POWER(735.18335 * Var1, 3)) - (POWER(4749.7294 * Var1, 4))) .. If i calculate it on excel, the result should be, 1.74269E-06. Thats why im asking, what is the best data type for this kind of value? – Haminteu May 07 '14 at 07:35