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
LVL 1
liversenAsked:
Who is Participating?
 
eszaqConnect With a Mentor Commented:
Unless I am missing something you do not need to cast or convert. You can use round().
SELECT ROUND(123.4545, 2) returns  123.4500

And thus you need:
Select  ROUND(30.94/0.178*0.178, 2)
0
 
chapmandewCommented:
Select cast((30.94/0.178*0.178) as decimal(18,2))
0
 
chapmandewCommented:
>>Unless I am missing something you do not need to cast or convert. You can use round().

Round is doing the cast for you.  :)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
eszaqCommented:
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.
0
 
chapmandewCommented:
;)
0
 
liversenAuthor Commented:
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)+(30.94-100000),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.


0
 
eszaqCommented:
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.94-100000),3)  from login
0
 
eszaqConnect With a Mentor Commented:
Opps
rounded to the wrong digit. You need second, right?
ROUND((30.94-(30.94-100000)/0.178*0.178)+(30.94-100000),2)
0
 
liversenAuthor Commented:
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
0
 
eszaqCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.