Magic's and Validation Nature of a Round Function in SQL Server
In this article you will learn how to use Magic's and validation nature of a Round function in SQL Server
I happened to work in the round function. I found some interesting / peculiar
nature of round fucntion. Hope you will enjoy this article. Can you believe you
can use round function to validate the input values???????Are you ready for the
The syntax for the round function is
Length indicates the rounding at the precion level (If positive)...Or else it
will round the actual value (If the value is negative)
You will understand it from the below sample queries.
Now, I am trying to round the below value. Let's see how it goes....
SELECT ROUND(19.46,0 ) as total
In above query, am informing sql to round the value with no precisions.
SELECT ROUND(19.46123,1 ) as total
In above query, am informing sql to round the value with 1 precisions. (.46)
value is round off to (.5).
If the value is (.44) it will rounded off to (.40). Since its (.46) which is
greater than (.45) the value is (.50)
SELECT ROUND(19.46123,2 ) as total
In above query, am informing sql to round the value with 2 precisions.
SELECT ROUND(19.46123,-1 ) as total
In above query, am informing sql to round the value not the precisions.
If the value is negative, the rounding operation will start from the left side.
Here, it's trying the value 19 and not
checking the precisions (.46123)
SELECT ROUND(19.46123,-2 ) as total
In above query, am informing sql to round the value. If the length is equal or
more than the value. Then round function will make the value as 0.
So, If two digit value is mandatory and if the user is trying to give 1 digit.
The above query will help us to evaluate the inputs.
Good one right!!!!!
Venkatesan Prabu .J