CODEWITHSUNDEEP
sql-servert-sql
Josh
Josh

Reputation: 13

TSQL Round() function not working the same way twice

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

Upvotes: 0

Views: 223

Answers (3)

Martin Brown
Martin Brown

Reputation: 25310

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.

Upvotes: 0

Shantanu Gupta
Shantanu Gupta

Reputation: 21198

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.

Upvotes: 1

benjamin moskovits
benjamin moskovits

Reputation: 5458

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.

Upvotes: 0

Related Questions