TSQL Round() function not working the same way twice

Question

I cannot get the Round() function to behave properly using the same logic.

Code:

Declare @Cycle AS INT;
SELECT @Cycle = CycleTime 
FROM MachineStatus 
WHERE MachineNumber = @MN;

UPDATE Job_Op_Time
SET Act_Run_Qty = Act_Run_Qty + 1, Act_Run_Hrs = ROUND(CAST(((Act_Run_Qty + 1) * @Cycle) AS FLOAT)/3600,2),
Act_Run_Labor_Hrs = ROUND(CAST(((Act_Run_Qty + 1) * @Cycle) AS FLOAT)/3600,2)
WHERE Job_Operation = @Op AND Work_Date = DATEADD(dd,0,DATEDIFF(dd,0,@L))

The above lines calculating Act_Run_Hrs and Act_Run_Labor_Hrs should give the same result but they do not (they are the same code!). As an example, Act_Run_Hrs will be set to 6 and Act_Run_Labor_Hrs will be set to 6.96.

Thanks

EDIT: If it matters, this is code is in an after insert trigger

That doesn't sound likely. Are the fields you're updating of the same type (and scale+precision)? Are you the only one updating that table? — Luaan, Apr 13 '15 at 14:58
Yes, both the fields are of type float. Yes, this code is an if statement and only i update this record. Users could be editing the table at the same time, but not the records i am acting on. This problem always repeats itself. — Josh, Apr 13 '15 at 15:02
Are you *sure* you copied the update statement correctly? The only possible failure I can see is if this breaks by your change of `Act_Run_Qty`. I'm not actually sure if that's a safe operation. Maybe one of the `round`s gets `Act_Run_Qty` already incremented? Try running it as two statements (first the `Act_Run_Qty` increment, then the `Act_Run_Hrs` and `Act_Run_Labor_Hrs`) just to be sure. Or maybe try wrapping the update in an explicit serialized transaction - I'm not sure if what you're doing is thread-safe anyway. — Luaan, Apr 13 '15 at 15:10
I suspect your trigger has bigger problems. You have scalar variables all over here which makes me believe your trigger cannot handle the set based operations required to make a trigger functional. Remember that triggers fire once per operation, not once per row. — Sean Lange, Apr 13 '15 at 15:15

score 1 · Answer 1 · edited May 23 '17 at 12:04

You should also take a look at round function on MSDN which has a third optional parameter function which by default is 0 performs a rounding however when any value other than 0 truncates the result after specified value.

ROUND ( numeric_expression , length [ ,function ] )

Why are you using float? Any specific reason. FLOAT is a culprit for you

However I your issue is with CAST operation when converting to float. Float is an approximate numeric data type. This will not guarantee value returned to be same everytime.

EDIT:

MSDN says:

The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

Even though float is an approximation, it should always give you the same approximation. If you cast an integer to a float even if the float can't store the exact number it should always end up with the same wrong value. — Martin Brown, Apr 13 '15 at 17:10

benjamin moskovits · Answer 2 · 2015-04-13T15:24:36.313

0

SQL Server has a concept of deterministic which means that given the same inputs you always get the same exact results. On the list (at https://technet.microsoft.com/en-us/library/aa214775%28v=sql.80%29.aspx) of deterministic functions SQL Server includes the round function. If it did not work as advertised (round is very widely used and has been around forever) believe me this would have been discovered already.

This is one case where it is almost certainly a case of different inputs or settings. Since its in a trigger your changes/inserts are in the table 'inserted' I would use a combination of inserted and the output clause to see what exactly is being inputted to the round function and what the output is. Its not what you think.

edited Apr 13 '15 at 15:24

answered Apr 13 '15 at 15:01

benjamin moskovits

5,261
1
12
22

I agree with you that it is my fault, but i cannot get this to work properly. As you can see, the input is the same both times. I guess that just leaves settings, but both of the fields i input into are of type float. – Josh Apr 13 '15 at 15:07
I hate when people use 'fault' in discussing programming issues. We are just trying to do our jobs in the time given to us. Please see my updated solution. – benjamin moskovits Apr 13 '15 at 15:25

Martin Brown · Answer 3 · 2015-04-13T17:11:37.973

What you are suggesting does not make a lot of sense so there must be something outside the query that is causing this.

My guess is that the types of the columns are:

Act_Run_Hrs integer
Act_Run_Labor_Hrs float

In this case sql sever will do an implicit cast (like the following) from float to integer for Act_Run_Hrs which would give you the result you have seen.

UPDATE 
    Job_Op_Time
SET 
    Act_Run_Qty =       Act_Run_Qty + 1, 
    Act_Run_Hrs =       CAST(ROUND(CAST(((Act_Run_Qty + 1) * @Cycle) AS FLOAT)/3600,2) as int),
    Act_Run_Labor_Hrs = ROUND(CAST(((Act_Run_Qty + 1) * @Cycle) AS FLOAT)/3600,2)
WHERE 
    Job_Operation = @Op 
    AND Work_Date = DATEADD(dd,0,DATEDIFF(dd,0,@L))

Another possibility is that another transactions is getting in and altering the data between this update running and you looking at the result.

TSQL Round() function not working the same way twice

3 Answers3