Post reply

Dividing Integers behaviour

  • May 6, 2022 at 11:38 am

    #4027986

    I am trying to find documentation or an explanation as to the behaviour when dividing one integer by another .

    Take my code as example:

    declare @WeightInGrams int
    set @WeightInGrams = 164

    select @WeightInGrams, 
    @WeightInGrams/1000 AS [NoCast],
    CAST(@WeightInGrams AS decimal(18,3))/1000 AS [CastColumnOnly],
    @WeightInGrams / CAST(1000 AS decimal(18,3)) AS [CastValueOnly],
    CAST(@WeightInGrams AS decimal(18,3)) / CAST(1000 AS decimal(18,3)) AS [CastBoth],
    CAST( CAST(@WeightInGrams AS decimal(18,3)) / CAST(1000 AS decimal(18,3)) AS decimal(18,3) ) AS [DoubleCast]

    The result of [NoCast] I think I understand because both types are integer and the bit after the decimal is dropped. so 0.146 becomes 0 as a decimal.

    The result of [CastVariableOnly] and [Cast1000Only] and [CastBoth] I do not understand - they vary in the number of decimal places (one is 19 places, the other 20, and the other is 8 decimal places). And neither seem to have taken on board my cast to decimal(18,3) which I thought they would after reading about Data Type Precedence (where the decimal type takes precedence because it has a higher precedence than integer)

    I am trying to get a decimal to 3 decimal places when dividing one integer by another. Is the [DoubleCast] column the only way I can achieve this?

  • May 6, 2022 at 5:16 pm

    #4028166

    Part of the problem is that SQL Server implicitly converts the results to a higher precision than you specify for one or both of the operands.  If it didn't do that you would end up with invalid results due to rounding and/or truncation.

    The double-cast isn't necessary here - you could do the following:

    declare @WeightInGrams int
    set @WeightInGrams = 164

    select @WeightInGrams, 
    CAST(@WeightInGrams / 1000.0 AS decimal(18,3))

    If your divisor is a fixed value - either a parameter or column, you could do something like this:

    declare @WeightInGrams int = 164
          , @divisor int = 1000;

    select @WeightInGrams, 
    CAST(@WeightInGrams / (@divisor * 1.0) AS decimal(18,3))

    With that said - the above constructs will include implicit converts so you can't get away from the actual conversion, but you can avoid explicitly coding each conversion.  The implicit conversion for an integer data type will be to a numeric(10,0) - and since a division of the largest integer can never be larger than 10 digits then converting to a numeric that allows 15 digits to the left of the decimal isn't necessary.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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