CODEWITHSUNDEEP
sqlsql-server-2008decimal
RMN
RMN

Reputation: 752

SQL Format as of Round off removing decimals

I have a calculated field 'MySum' in inner query whose value after calculation is 58.

I need to get 20% of this value.

If I give: 

MySum * 20 /100, I get 11

If I give: 

((20 * CAST(MySum as decimal(6,2)))/100) , I get 11.60000

If I give 

Round(((20 * CAST(MySum as decimal(6,2)))/100), 2), I still get 11.60000

I want that,

If result comes 11.6, it should display 12 and if result is 11.4, it should display 11.

I want to Rounded off values. Any function for that ?

Upvotes: 10

Views: 167663

Answers (4)

4 Leave Cover
4 Leave Cover

Reputation: 1276

SELECT CONVERT(INT, 11.4)

RESULT: 11

SELECT CONVERT(INT, 11.6)

RESULT: 11

Upvotes: -1

Diego
Diego

Reputation: 36146

check the round function and how does the length argument works. It controls the behaviour of the precision of the result

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

use ROUND () (See examples ) function in sql server

select round(11.6,0)

result:

12.0

ex2:

select round(11.4,0)

result:

11.0

if you don't want the decimal part, you could do

select cast(round(11.6,0) as int)

Upvotes: 38

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

CAST(Round(MySum * 20.0 /100, 0) AS INT)

FYI

MySum * 20 /100, I get 11

This is because when all 3 operands are INTs, SQL Server will do perform integer maths, from left to right, truncating all intermediate results.

58 * 20 / 100 => 1160 / 100 => 11 (truncated from 11.6)

Also for the record ROUND(m,n) returns the result to n decimal places, not n significant figures.

Upvotes: 1

Related Questions