Alternatives · Basic Logics · Data Management · Real Scenario Logic's

Truncate Extra Decimal Places to Exact Scale in SQL Server

Introduction

In this blog post, we are going to see How we can Truncate Extra Decimal Places to Exact Scale in SQL Server. We can also say this as, Scale Only Round Off and Non-value Round Off.

Method 1 – Using ROUND()

SQL Script

--Method 1 - Using ROUND() 
DECLARE @Test_Table AS TABLE (Test_Column DECIMAL(20,5));
INSERT INTO @Test_Table
SELECT 1.989 UNION ALL
SELECT 32.7868 UNION ALL
SELECT 999.9;

SELECT 
	Test_Column AS Input,
	ROUND(Test_Column,2) AS Current_Output,
	CAST(ROUND(Test_Column,2,1) AS DECIMAL(20,2)) AS Expected_Output
FROM @Test_Table;

Image Pic

Using ROUND Function

NOTE –

In the above scenario, we have used DECIMAL Datatype. In fact, Decimal and Numeric are synonyms and those can be used interchangeably. Both of these have fixed Precision and Scale. eg., DECIMAL(p,s) and NUMERIC(p,s) where pprecision; sscale

The ROUND function Returns a Numeric value/expression, Rounded/Truncated to the specified length or precision. In this function, the first argument is the numeric data that the user gives and the second argument is the length, to which the numeric data needs to be rounded and the third argument is the optional one that determines the type of operation like rounding(0) or truncating(non-zero). When the third argument is omitted ,by default it has a value of 0 that means numeric_data needs to be rounded. When a value other than 0 is specified, then the numeric_data needs to be truncated.

If the third parameter <> 0, ROUND function will do Truncating operation instead of Rounding operation.

By default , the third parameter has 0 that means Rounding operation is the default type of operation.

Method 2 – Using ODBC Scalar Function of TRUNCATE()

SQL Script

--Method 2 - Using ODBC Scalar Function of TRUNCATE()
DECLARE @Test_Table AS TABLE (Test_Column DECIMAL(20,5));
INSERT INTO @Test_Table
SELECT 1.989 UNION ALL
SELECT 32.7868 UNION ALL
SELECT 999.9;

SELECT 
	Test_Column AS Input,
	ROUND(Test_Column,2) AS Current_Output,
	CAST({fn TRUNCATE(Test_Column, 2)} AS DECIMAL(20,2)) AS Expected_Output
FROM @Test_Table;

Image Pic

Using ODBC Scalar Function of TRUNCATE

NOTE –

Here, we have used the ODBC (Open DataBase Connectivity) Scalar Function of TRUNCATE Function. These statements are interpreted by SQL Server. Like this, many other ODBC Scalar functions are also exists in SQL Server.

Conclusion

Thus, we saw the above two simple methods for truncating the extra decimals to exact scale in SQL Server. The above two mentioned methods, works fine in both On-Premises and Azure SQL DB. Unit Tested as well.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s