Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 968
  • Last Modified:

Round function in SQL produces incorrect answer

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
pauldonson
Asked:
pauldonson
  • 3
1 Solution
 
adatheladCommented:
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
 
adatheladCommented:
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
 
pauldonsonAuthor Commented:
That will do for me! Thanks for the quick response.
0
 
adatheladCommented:
No problem
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now