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

# 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
• 3
1 Solution

Commented:
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

Commented:
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 Commented:
That will do for me! Thanks for the quick response.
0

Commented:
No problem
0

## Featured Post

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