?
Solved

Round function in SQL produces incorrect answer

Posted on 2005-03-16
4
Medium Priority
?
964 Views
Last Modified: 2008-03-06
Can anyone explain why the following SQL statement generates the answer 38.950000000000003

when it should be 38.96?

declare @inv_hours_Std as float
declare @inv_rate_Std as money

set @inv_hours_std = 5.25
set @inv_rate_std = 7.42

select round(@Inv_hours_std * @Inv_rate_std,2)

yet

select round(5.25*7.42,2)

produces 38.96


Can anyone help, this is an extract from code that generates invoices and half the lines are a penny out!!
0
Comment
Question by:pauldonson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 13556401
This is due to the datatypes being used

i.e. quote from BOL about FLOAT datatype:
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.


To get round this, you could convert both values in the calculation to DECIMAL.
e.g. assuming  the original values contain a maximum of 5 digits after the decimal place:

select round(cast(@Inv_hours_std as decimal(18,5)* cast(@Inv_rate_std as decimal(18,5),2)
0
 
LVL 23

Accepted Solution

by:
adathelad earned 1000 total points
ID: 13556413
Sorry, missed some closing brackets:
select round(cast(@Inv_hours_std as decimal(18,5))* cast(@Inv_rate_std as decimal(18,5)),2)
0
 

Author Comment

by:pauldonson
ID: 13556453
That will do for me! Thanks for the quick response.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13556536
No problem
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

752 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