SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Type Decimal Or Float
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samir.first
Starting Member

Egypt
34 Posts

Posted - 11/26/2013 :  03:53:46  Show Profile  Reply with Quote
How is The Best Decimal Or Float Or Others when Result Function

samir.first
Starting Member

Egypt
34 Posts

Posted - 11/26/2013 :  04:58:19  Show Profile  Reply with Quote
I need Help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/26/2013 :  05:32:37  Show Profile  Reply with Quote
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
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 11/27/2013 :  02:35:21  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  04:44:58  Show Profile  Reply with Quote
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
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 11/30/2013 :  02:19:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/30/2013 :  03:00:17  Show Profile  Reply with Quote
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
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 11/30/2013 :  04:29:43  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/30/2013 :  04:39:50  Show Profile  Reply with Quote
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
Go to Top of Page

samir.first
Starting Member

Egypt
34 Posts

Posted - 11/30/2013 :  06:16:46  Show Profile  Reply with Quote
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
Go to Top of Page

waelmg50
Starting Member

Egypt
2 Posts

Posted - 12/01/2013 :  04:30:08  Show Profile  Reply with Quote
Is there a way to get the exact result of the query without any approximation?

Be the change that you want to be.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/01/2013 :  11:10:22  Show Profile  Reply with Quote
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
Go to Top of Page

waelmg50
Starting Member

Egypt
2 Posts

Posted - 12/04/2013 :  23:17:24  Show Profile  Reply with Quote
Yes, without approximation.
The exact answer of the query 1 not 0.999999

Be the change that you want to be.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/05/2013 :  01:43:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000