Type Decimal Or Float - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Type Decimal Or Float

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2013-11-26 : 03:53:46

How is The Best Decimal Or Float Or Others when Result Function

samir.first
Starting Member

34 Posts

Posted - 2013-11-26 : 04:58:19

I need Help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 05:32:37

I didnt get your question.
If you're asking on which datatype to use as return type for your function only thing we can say is it depends on requirement
Use Decimal if you're sure of the scale value (no of places after decimal digits) of the result. Use Float for approximate decimal values and use int if you dont have decimal result at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

samir.first
Starting Member

34 Posts

Posted - 2013-11-27 : 02:35:21

The problem is that we use decimal(38,18) to have corrected data but the data is approximated in the query. But when we use float datatype the data is displayed without any approximation. But we are afraid from using float data type because Microsoft stated that this type is approximate data type so we can have a problem according to this script :
DECLARE @fr FLOAT
SET @fr = 1000000000000000000
SElECT CAST(@fr + 1 AS BIGINT)

The rseult is :
1000000000000000000
not
1000000000000000001

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 04:44:58

quote:
Originally posted by samir.first

The problem is that we use decimal(38,18) to have corrected data but the data is approximated in the query. But when we use float datatype the data is displayed without any approximation. But we are afraid from using float data type because Microsoft stated that this type is approximate data type so we can have a problem according to this script :
DECLARE @fr FLOAT
SET @fr = 1000000000000000000
SElECT CAST(@fr + 1 AS BIGINT)

The rseult is :
1000000000000000000
not
1000000000000000001

data will get rounded if significant digitd after decimal overshoots your set scale value(ie 18)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

samir.first
Starting Member

34 Posts

Posted - 2013-11-30 : 02:19:08

DECLARE @Result DECIMAL (18,10),@no1 DECIMAL (18,10) , @no2 DECIMAL (18,10) , @no3 DECIMAL (18,10) , @Value DECIMAL (18,10)
SET @Value = 130
SET @no1 = 20/@Value
SET @no2 = 50/@Value
SET @no3 = 60/@Value
SET @Result = @no1 + @no2 + @no3
SELECT @Result
I Need Result = 1 not 0.999999

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 03:00:17

The reason is because decimal fields cant store full result as per defined scale value
you need to use below if you want to get rounded result

ROUND(@Result,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

samir.first
Starting Member

34 Posts

Posted - 2013-11-30 : 04:29:43

Dear visakh16
The problem is that i want to get the exact result not the rounded result.
and the correct result of the query is 1, Why it's values gives 0.99999999999999?
When we use the float data type it gives the correct result 1
but i am afraid from using float data type because it is approximated in some cases as posted before.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 04:39:50

quote:
Originally posted by samir.first

Dear visakh16
The problem is that i want to get the exact result not the rounded result.
and the correct result of the query is 1, Why it's values gives 0.99999999999999?
When we use the float data type it gives the correct result 1
but i am afraid from using float data type because it is approximated in some cases as posted before.

Because in decimal datatype it can store only upto predefined number of decimal places (in your case 10). So in case actual results have more precision then it would round and store it upto maximum it can hold. So when you add them back it gives you result based on what it stored rather than upto full precision which is why you get 0.999999 instead of 1. so your only bet is to use round to get next whole number or use CEILING function.


SELECT CEILING(@Result),ROUND(@Result,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

samir.first
Starting Member

34 Posts

Posted - 2013-11-30 : 06:16:46

DECLARE @Result float ,@no1 float , @no2 float , @no3 float, @Value float
SET @Value = 130
SET @no1 = 20/@Value
SET @no2 = 50/@Value
SET @no3 = 60/@Value
SET @Result = @no1 + @no2 + @no3
SELECT @Result
--======================================================================================
DECLARE @Result REAL ,@no1 REAL , @no2 REAL , @no3 REAL, @Value REAL
SET @Value = 130
SET @no1 = 20/@Value
SET @no2 = 50/@Value
SET @no3 = 60/@Value
SET @Result = @no1 + @no2 + @no3
SELECT @Result

waelmg50
Starting Member

3 Posts

Posted - 2013-12-01 : 04:30:08

Is there a way to get the exact result of the query without any approximation?

Be the change that you want to be.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-01 : 11:10:22

quote:
Originally posted by waelmg50

Is there a way to get the exact result of the query without any approximation?

Be the change that you want to be.

do you mean without rounding?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waelmg50
Starting Member

3 Posts

Posted - 2013-12-04 : 23:17:24

Yes, without approximation.
The exact answer of the query 1 not 0.999999

Be the change that you want to be.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-05 : 01:43:27

quote:
Originally posted by waelmg50

Yes, without approximation.
The exact answer of the query 1 not 0.999999

Be the change that you want to be.

use Int datatype then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources