round decimal pls help

[hokky]

Guys,

I got simple issue here,

DECLARE @Row_ID				int,
	@Vol				float(2)

SET @Row_ID = 1

WHILE @Row_ID<=5
BEGIN

select @Row_ID Row_ID

SET @Vol = CASE
 	WHEN @Row_ID = 1 then '0.1'
	WHEN @Row_ID = 2 then '0.25'
	WHEN @Row_ID = 3 then '0.5'
	WHEN @Row_ID = 4 then '0.75'
	WHEN @Row_ID = 5 then '0.9'
	ELSE 'Invalid Row ID'	
END

select @Vol Volatility
	SET @Row_ID = @Row_ID + 1
END

The result is

Row_Id
5

Volatility
0.89999998

I have tried decimal, real but still doesn't fix my problem.
Can anyone tell me ?

 

[PDreyer]

Use the round function

Note:

float is an approximate numeric datatype and should not be used where absolute precision is required. This type is useful for applications that need large numbers but do not need precise accuracy.

When you compare floating-point values. Check that the difference between them is small compared to the size of the numbers, rather than look for exact equality (i.e. use rounding).

If you require very accurate numbers, use the decimal data type (e.g. in financial applications).

 

[PDreyer]

For more info see What Every Computer Scientist Should Know About Floating-Point Arithmetic

 

[SQLSister]

You said you tried decimal and it didn't fix your problem, so what exactly is the problem becasue it ceratinly should give you an answer of .9 for volatility onthe last round of the while loop.

"NOTHING is more important in a database than integrity." ESquared

 

[djj55]

Hello, I used the following and it worked.

DECLARE @Row_ID                int,
    @Vol  [COLOR=red]decimal(2,2)[/color]

SET @Row_ID = 1

WHILE @Row_ID<=5
BEGIN

select @Row_ID Row_ID

SET @Vol = CASE
     WHEN @Row_ID = 1 then 0.1
    WHEN @Row_ID = 2 then 0.25
    WHEN @Row_ID = 3 then 0.5
    WHEN @Row_ID = 4 then 0.75
    WHEN @Row_ID = 5 then 0.9
    ELSE 1    
END

select @Vol Volatility
    SET @Row_ID = @Row_ID + 1
END

However, this does give non-existent precision. (0.90)

djj

 

[hokky]

decimal(2,2) doesn't work.

the result is .90 instead of 0.90

I want 0.90,

anymore idea?

 

[djj55]

Can you tried a string variable (char, varchar, nvarchar)?

djj

 

[AlexCuse]

SQL Server does not care about whether you see a 0 before the decimal place or not, and frankly it shouldn't.

Add the zero in your presentation layer if you MUST have it.

And definitely don't use a string data type if you are going to need to do calculations on this value.

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[ESquared]

If this is the same issue as your other thread then you shouldn't be using a loop. Your task can be accomplished with a query. Please go back to that thread and keep working on it.