Calculating Decimals when dividing whole numbers

  • OK, I must be missing something obvious. Why does only the last calculation return a fractional value ??

    select cast((7 /2)as decimal(9,2))-- 3.00

    select cast((69 /60) as decimal(6,2))-- 1.00

    select cast((138 /60) as decimal(4,2))-- 2.00

    select cast((137418247 /10731824)as decimal(9,2))-- 12.00

    select cast((13737418247 /1073741824) as decimal(9,2))-- 12.79

  • Not sure why the last is returning decimals, but the reason all the earlier ones are not is because you're dividing two ints. The result of that is also an int, which you're then casting to a decimal.

    Cast one of the inputs to decimal and you'll see decimal places.

    It could be that the last one the two values are two large to be ints so are considered to be float (maybe) instead.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Greetings,

    The SQL engine will convert an integer number to a decimal number automatically when performing a math function and when one of the parts is a decimal number. In this case, your result is a decimal.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ee53c5c8-e36c-40f9-8cd1-d933791b98fa.htm

    If you look about 1/3 down the page - under Remarks, it will talk about implicit conversions.

    Two expressions can be combined by an operator if they both have data types supported by the operator and at least one of these conditions is true:

    The expressions have the same data type.

    The data type with the lower precedence can be implicitly converted to the data type with the higher data type precedence.

    Have a good day.

    Terry Steadman

  • This is in books online:

    SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

    So for your query to work you will have to do the following:

    select cast((cast(13737418247 as bigint) /1073741824) as decimal(9,2))

  • This is in books online:

    SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

    So for your query to work you will have to do the following:

    select cast((cast(13737418247 as bigint) /1073741824) as decimal(9,2))

Viewing 5 posts - 1 through 4 (of 4 total)

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