how to convert a negative numeric to postive in sql

November 17, 2023

I want to convert -2.07 to 207 but using ABS() as shown; it returns 200 not 207.
Please help me understand what I am missing here. Thanks.

 declare @ReimbursementCents int,
 @journey_fare numeric(7,2)
 SET @journey_fare = -2.07

 SET @ReimbursementCents = convert(int,ABS(@journey_fare)) * 100
 if (@ReimbursementCents > 0)
 print @ReimbursementCents

>Solution :

It looks like the issue lies in the order of operations and the placement of the multiplication by 100. Currently, you are converting the absolute value of @journey_fare to an integer and then multiplying it by 100, which might give unexpected results for negative values.

To achieve the desired result of converting -2.07 to 207 using ABS(), you should first multiply by 100 and then convert to an integer.
Here’s the corrected code:

DECLARE @ReimbursementCents INT,
        @journey_fare NUMERIC(7,2);

SET @journey_fare = -2.07;

-- Multiply by 100 and then convert to integer
SET @ReimbursementCents = CONVERT(INT, ABS(@journey_fare) * 100);

IF (@ReimbursementCents > 0)
    PRINT @ReimbursementCents;

Now, this code will correctly output 207 for the given @journey_fare value of -2.07. The key change is multiplying by 100 before applying the ABS() function.