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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Select cast((30.94/0.178*0.178) as decimal(18,2))
0
eszaqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
eszaqCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.