Magic's and Validation Nature of a Round Function in SQL Server

I happened to be working with the round function. I found some interesting / peculiar nature of the round fucntion. Hope you will enjoy this article. Can you believe you can use the round function to validate input values??????? Are you ready for the excitement??

The syntax for the round function is

round(value, length)

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 following sample queries.

Now, I am trying to round the following 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.

Interesting right!!!!

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!!!!!


Cheers,

Venkatesan Prabu .J