Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to solve decimal error during math operations in t-sql?

Posted on 2008-11-13
10
Medium Priority
?
316 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:liversen
  • 5
  • 3
  • 2
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22951145
Select cast((30.94/0.178*0.178) as decimal(18,2))
0
 
LVL 8

Accepted Solution

by:
eszaq earned 1500 total points
ID: 22952469
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22952485
>>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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:eszaq
ID: 22952533
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22952604
;)
0
 
LVL 1

Author Comment

by:liversen
ID: 22958260
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
 
LVL 8

Expert Comment

by:eszaq
ID: 22958313
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
 
LVL 8

Assisted Solution

by:eszaq
eszaq earned 1500 total points
ID: 22958321
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
 
LVL 1

Author Comment

by:liversen
ID: 22958362
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
 
LVL 8

Expert Comment

by:eszaq
ID: 22958419
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question