two digits after decimal ?

Analyst_SQL 3,531 Reputation points
2021-06-18T19:04:57.71+00:00

I want two digits after decimal.

cast(B.Bweight as decimal(10,2))/cast(C.iWeight as decimal(10,2))*100 [Percentage]

currently i am getting below result

0.9021512838300
but i want

0.90

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,584 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.7K Reputation points MVP
    2021-06-18T21:07:10.787+00:00

    The resulting data type when you perform an operation on two decimal values is a complicated story, and I don't remember the rules by hart. But as you have experienced, dividing two decimal(10,2) values does not result in a new decimal(10,2) value.

    I would write this as

    cast(     cast(B.Bweight as float)/cast(C.iWeight as float)*100 as decimal(10,2)) AS [Percentage]
    

    The reason I would cast to float before the division is that this the best accuracy for the division as such. But depending on the actual data types of the Weight columns, this may be an over kill. The important is that you need to cast the final result to decimal(10,2).

    0 comments No comments

0 additional answers

Sort by: Most helpful