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

Introduction

I hope you will enjoy this article. I happened to be working with the round function. I found some exciting/peculiar nature of the round function. 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 length indicates the rounding at the precision level (If positive)...Or else it will round the actual value (If the value is negative).

round(value, length)

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

I am informing SQL to round the value with no precisions in the above query.

SELECT ROUND(19.46123,1 ) as total

In the above query, I inform SQL to round the value with 1 precision. (.46) value is rounded off to (.5).

If the value is (.44), it will be rounded off to (.40). Since it's (.46), which is greater than (.45), the value is (.50)

SELECT ROUND(19.46123,2 ) as total

I am informing SQL to round the value with 2 precisions in the above query.

SELECT ROUND(19.46123,-1 ) as total

In the above query, I am informing SQL to round the value, not the precisions.

The rounding operation will start from the left side if the value is negative. Here, it's trying the value 19 and not Checking the precisions (.46123)

SELECT ROUND(19.46123,-2 ) as total

In the above query, I am informing SQL to round the value. Suppose the length is equal to or more than the value. Then the round function will make the value 0.

Summary

So, If the two-digit value is mandatory and the user is trying to give 1 digit. The above query will help us to evaluate the inputs.

Cheers,

Venkatesan Prabu .J