liversen
asked on
How to solve decimal error during math operations in t-sql?
Hi
I have a quite complex query which is not behaving as I want it to.
I have boiled it down to a small example that shows my problem:
Select 30.94/0.178*0.178
it returns 30.939999872
I want it to return 30.94 or 30.94000000000
Can I do some kind of conversion to other datatypes in the select statement orwhat should I do. I need it to be able to do this kind of calculation without making decimal errors.
Thanks in advance
I have a quite complex query which is not behaving as I want it to.
I have boiled it down to a small example that shows my problem:
Select 30.94/0.178*0.178
it returns 30.939999872
I want it to return 30.94 or 30.94000000000
Can I do some kind of conversion to other datatypes in the select statement orwhat should I do. I need it to be able to do this kind of calculation without making decimal errors.
Thanks in advance
Select cast((30.94/0.178*0.178) as decimal(18,2))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Unless I am missing something you do not need to cast or convert. You can use round().
Round is doing the cast for you. :)
Round is doing the cast for you. :)
chapmandew :)
CAST (technically) converts an expression of one data type to another (even if you are converting between the same types)
ROUND() is mathematical function.
CAST (technically) converts an expression of one data type to another (even if you are converting between the same types)
ROUND() is mathematical function.
;)
ASKER
Thanks. That solves it for my simple example unfortunately if I add a bit more complexity I can't get it to work:
Here's another example which again is simplyfied a bit:
SELECT ROUND((ROUND((30.94-(30.94 -100000))/ 0.178,0,1) *0.178)+(3 0.94-10000 0),3)
I want it to return 30.94. It does not. Instead it returns 30.806000000
I have tried to put this CAST function on it. It does not change anything :o(
I must admit that I do not have the full overview of the round function.
Any help is very much appreciated.
Here's another example which again is simplyfied a bit:
SELECT ROUND((ROUND((30.94-(30.94
I want it to return 30.94. It does not. Instead it returns 30.806000000
I have tried to put this CAST function on it. It does not change anything :o(
I must admit that I do not have the full overview of the round function.
Any help is very much appreciated.
Why are you nesting round(round())? This is causing wrong result.
Here:
SELECT top 1 ROUND((30.94-(30.94-100000 )/0.178*0. 178)+(30.9 4-100000), 3) from login
Here:
SELECT top 1 ROUND((30.94-(30.94-100000
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I round twice because I'm also grouping by that column in the complete query and I want to group in intervals of 0.178 size.
Anyway I think I have fixed it by removing the 100000 offset. So now it seems to work.
Thanks to all of you for your efforts
Anyway I think I have fixed it by removing the 100000 offset. So now it seems to work.
Thanks to all of you for your efforts
but if you nest your rounds you are doomed to get your calculations wrong. Even if you round down to the same digit. Even simple a+b will give you difference with nesting functions. Here is simplified example:
select round((5.54*5.54),2) as simple, round(5.54*round(5.4,2),2) as nested_rnd
With small numbers it might be not as obvious, but it's just matter of meeting certain condition when error will pop up.
select round((5.54*5.54),2) as simple, round(5.54*round(5.4,2),2)
With small numbers it might be not as obvious, but it's just matter of meeting certain condition when error will pop up.