Rounding issues

Question

Post reply

Rounding issues

Sandeep2

SSC Enthusiast

Points: 112
More actions
November 1, 2011 at 2:44 pm

#390738

I am having a random rounding issue and wondering if someone has dealt with this.
I have two fields PayUnit float = 31.93 , PayRate Money =8.50
PayUnit* PayRate= 31.93*8.5= 271.405
With the code below - it is randomly rounding it to 271.41 sometimes and 271.40 other times. What am I doing wrong>
SELECT ROUND(CAST(tei.PayUnit AS MONEY)* tei.PayRate, 2)
Any ideas?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323753 More actions · Answer 1

well remember floats are stored as approximations, so i'd suspect witht eh full details, that's where the issue lies.

SQL

with the one exampel you offered, i don't see anything specific to offer;

/*

--Results:

PayUnitPayRate(No column name)(No column name)

31.938.50271.405271.41

*/

With tei AS

(

SELECT PayUnit = CONVERT(float,31.93),

PayRate = CONVERT(Money, 8.50)

)

SELECT PayUnit,

PayRate,

PayUnit * PayRate,

ROUND(CAST(tei.PayUnit AS MONEY)* tei.PayRate, 2)

from tei

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sandeep2 SSC Enthusiast Points: 112 More actions · Answer 2

Thanks for the response. I was aware of the float issue and the hope was the casting the Payunit to Money would solve that. I have reconfirmed the Payrate is Money.

One thing to add to this is that this gets rounded the right way on multiple servers however I am having this issue on a specific server and on that too it is random.