April 02, 2010

Automatically Round off numbers to the nearest digit




I was solving a requirement where data entered into a table contained decimals. However the user had a requirement of automatically rounding off the decimals to the nearest digit before inserting into the table. The SQL statements could not be changed to make use of the Round() function. So one of the ways of solving this was to use the numeric datatype in the table definition as shown below:

DECLARE @TmpTable TABLE
(
id int,
amount numeric(7,0)
)

INSERT INTO @TmpTable VALUES (1, 5.556233)
INSERT INTO @TmpTable VALUES (1, 2.123953)
INSERT INTO @TmpTable VALUES (1, 3.753433)

SELECT * FROM @TmpTable

OUTPUT

image

As you can see, data stored into the table automatically gets rounded off.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

2 Responses to "Automatically Round off numbers to the nearest digit"
  1. Dave Levy said...
    April 2, 2010 at 11:01 AM

    Seems kind of risky to rely on implied functionality instead of just using the Round (http://msdn.microsoft.com/en-us/library/ms175003.aspx) statement.

    Does using the temp table buy you anything else?

  2. Suprotim Agarwal said...
    April 2, 2010 at 7:58 PM

    Dave: Nice point. The client did not want to change the sql statement. They were ok making changes in the schema instead, hence the solution. I have added that point to the post.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions