Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Remove decimal places without rounding

By Robert van Kooten,

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.

Total article views: 206 | Views in the last 30 days: 1
Related Articles

Displaying Fewer Decimal Places

Displaying fewer decimal places


Decimal places for totals

SSRS Reports can not set decimal places


format related problem in SSRS: to show upto # decimal places

format related problem in SSRS: to show upto # decimal places


decimal places in depreciation

dealing with getting decimals to be of proper length.


convert a number to decimal

convert a number to decimal


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones