Let's have a look at the picture.
The result is different even though the expression is the same.
Why does this happen?
I have to follow excel result, what should I have do with sql server?
4
-
@MitchWheat Coz it's the same formula.Joshua Son– Joshua Son2014-03-20 08:37:14 +00:00Commented Mar 20, 2014 at 8:37
-
@MitchWheat Okay, but I have to find a solution to make it same as excel. That's my client's order no matter what.Joshua Son– Joshua Son2014-03-20 08:38:40 +00:00Commented Mar 20, 2014 at 8:38
-
round the results = 4,693,058Mitch Wheat– Mitch Wheat2014-03-20 08:39:13 +00:00Commented Mar 20, 2014 at 8:39
-
@MitchWheat I need to get 4,693,057.99610 from sql server.Joshua Son– Joshua Son2014-03-20 08:40:35 +00:00Commented Mar 20, 2014 at 8:40
Add a comment
|
1 Answer
No matter whatever the software is 1+1 will always yeild 2 and if its not you should check you calculation again. see below
SELECT ((4972000.0000) * (1.0000 - 4.4000/100.0000))
/ ((1.0000 + ((36.0000/365.0000)) * (13.0000 / 100.0000)))
RESULT: 4693057.996104
To get the result on upto four decimal places Use ROUND() function.
SELECT ROUND(((4972000.0000) * (1.0000 - 4.4000/100.0000))
/ ((1.0000 + ((36.0000/365.0000)) * (13.0000 / 100.0000))), 4)
RESULT: 4693057.996100
4 Comments
Joshua Son
Thanks for your answer. Could you explain why this kind of thinks happen?
M.Ali
My guess is that since you have only used two decimal places in your query sql server must have assumed it is DECIMAL(10,2) or NUMEIC(10,2) some numeric data type with 2 decimal places. but during calculation when a floating point value was yeild sql server did an implicit conversion to float datatype, which is an imaginary value. and will give you unexpected results.
Ben Thul
@M.Ali: s/imaginary/imprecise/ ?
M.Ali
Imaginary is an imaginary/imprecise representation of an actual number. There are infinite number of "numbers" that cannot be presented in exact decimal form so we use an imaginary/imprecise number to represent the actual number. If this makes any sense :)