getting decimal places when dividing integers

• brownguyr

  SSC Enthusiast

  Points: 137

  More actions

  July 3, 2003 at 11:03 am

  #80514

  I am new at this so if it is a dumb question please give me a break.

  I am dividing to columns that are integers, trying to get the decimal places on the division. The results come back as whole numbers. I tried to cast the results into a decimal format, but am still getting a whole number result. Any help would be greatly appreciated.

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  July 3, 2003 at 11:47 am

  #464116

  To work properly, the integers must have be converted to decimals first.

  Try these three scripts:

  select convert(decimal(5,2), 10/3)

  and

  select convert(decimal(5,2), 10.0/3.0)

  and

  select convert(decimal(3,2), (convert(decimal(3,1),10)/convert(decimal(3,1),3)))

  The second script requires YOU to manually add the decimal and the zero. The third script does the conversion for you.

  Your script can replace the 10 and the 3 with your column names or variables.

  -SQLBill

• guarddata

  SSCertifiable

  Points: 6583

  More actions

  July 3, 2003 at 11:55 am

  #464118

  Yes - and SQL is smart enough if only one of the arguments has been converted. You could also look at the result of:

  SELECT Convert( decimal(5,2), 10 ) / 3

  I generally do this in combination with SQLBill's third entry (since you are probably talking about variables or columns )

  SELECT Convert( decimal(5,2), Convert( decimal(5,2), <column or variable> ) / <value> )

  Guarddata-

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  July 3, 2003 at 12:21 pm

  #464125

  guarddata,

  Thanks. I wasn't aware (and didn't think to try it) that converting only one part would also work.

  I learned something new today.

  -SQLBill