sql - SQLServer CAST and divide 2 decimal numbers, followed by multiplication gives too many decimal places -

sql - SQLServer CAST and divide 2 decimal numbers, followed by multiplication gives too many decimal places -

i'm working in sql server 2008. have alias column i'm creating via next formula:

col1 / col2 * some_number

col1 , col2 nvarchars in table, they're integers. some_number integer. if blindly col1 / col2, 0 most. so, need cast them decimals. want there maximum of 2 decimal places after decimal point. here have currently:

cast(col1 decimal(10,2)) / cast(col2 decimal(10,2)) * 100

however, returns far more decimal places 2. how prepare code homecoming 2 decimal places?

according the documents scale of result given by:

s2 = max(6, s1 + p2 + 1)

where p2 represents precision of numerator , s2 represents scale of denominator

so when dividing 1 decimal(10, 2) can substitute in values:

s2 = max(6, 2 + 10 + 1) = 13

which corroborated simple example:

select cast(1 decimal(10,2)) / cast(1 decimal(10,2)) = 1.0000000000000 -- 13 decimal points

you need utilize cast on result cut down scale:

select cast(cast(col1 decimal(10,2)) / cast(col2 decimal(10,2)) * 100 decimal(10, 2))

i'd suggest if info type of column nvarchar, "but they're integers" bite bullet , alter column info type.

sql