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.

at

3 comments:

  1. Dave LevyApril 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?

    ReplyDelete
  2. Suprotim AgarwalApril 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.

    ReplyDelete
  3. KIRTAN KHATSURIYAAugust 21, 2017 at 12:52 PM

    what if i do not want round values? how can i stop it?

    ReplyDelete