Technical Article

Remove decimal places without rounding

,

There was a script here not too long ago which showed how to 'truncate' a number to two decimal places without rounding. This used a lot of casts to varchars and back.
The solution worked, but I knew there had to be a way of doing exactly this without having to go back and forth between numeric and character data. The result is the UDF below.

In words, here is what the script does:
- Round the number to the specified number of decimal places. This can go either up or down.
- If the result is bigger than the input value, calculate and substract the minimum value allowed by the number of decimal places specified.

The only 'tough' bit in the UDF is the last bit, calculating the minimum value allowed by the number of decimal places. To do this we use the following formula:

1 / 10 ^ Decimals
(^ taken as the power operator, T-SQL uses the Power() function for this instead)

Example: 1 / 10 ^ 2 = 1 / 100 = .01

Because SQL Server likes to see whole numbers as integers, we need to explicitly convert both arguments for the Power() function to floats in order to get a float result. If we don't do that .01 is cast to an integer resulting in 0.

CREATE FUNCTION RemoveDecimalsWithoutRounding
(@InputValue Float
,@Decimals Int
)

-- Get the rounded value
DECLARE @ReturnValue Float;
SET @ReturnValue = Round(@InputValue, @Decimals);

-- If the value is too high remove the smalles value allowed by the specified number of decimal places
IF @ReturnValue > @InputValue
SET @ReturnValue = @ReturnValue - (1 /  Power(Convert(Float, 10), Convert(Float, @Decimals)));

PRINT @ReturnValue;

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

New MDX Book Published

Fast Track to MDX gives readers all the necessary background information needed to write useful, powerful MDX expressions and introduces the most frequently used MDX functions and constructs. No prior knowledge is assumed and examples are used throughout the book to rapidly develop MDX skills to the point where a reader can solve real business problems. A CD containing examples from within the book, and a time-limited version of ProClarity, is included

2003-05-01

2,698 reads

SQLServerCentral Article

How to Build Dynamic Stored Procedures

Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing!

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.8 (5)

You rated this post out of 5. Change rating

2007-10-02 (first published: )

50,542 reads