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

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