SQL ROUND Function

The SQL ROUND function is a SQL Mathematical Function which is used to round a number to a specified length or precision.

It returns a numeric value.

It always returns a value. It returns 0, If length is negative and larger than the number of digits before the decimal point.

ROUND ( numeric_expression , length [ ,function ] )

numeric_expression: It can be a number or numeric data type expression. It can not be a Bit data type.

Length: It is the number of decimal places to which we want to round the numeric_expression. Length number can be positive and negative of type tinyint, smallint, or int.

    • If value for length parameter is a positive number, then Round function will round the numeric_expression to the number of the decimal point specified as length.
    • If value for length parameter is a positive number, Round function will round the numeric_expression to the left side of the decimal point.

Function: It is an optional parameter and specifies the truncation point of the value. The default value for this parameter is zero. If value is not specifed to this parameter then it rounds the numeric_expression. If any value other than 0 is specified to the function, then it truncates the numeric_expression.

Lets look at an example of using SQL Server Round Function.

Using Round function with positive Inetger and positive length

For positive integer with positive length, Round function always returns the same number without any rounding.

SELECT ROUND(5, 0) AS Round_0;
SELECT ROUND(5, 1) AS Round_1;
SELECT ROUND(5, 2) AS Round_2;
SELECT ROUND(15, 3) AS Round_3;
SELECT ROUND(155, 4) AS Round_4;

As you can see, round function returns same number without rounding.

Using Round function with positive Integer and negative length

For positive integer with Negative length, Rounds function rounds the number to the nearest tens place, and when length is larger than the number of digits before the decimal point, then Round function always returns 0.

SELECT ROUND(5, -1) AS Round_1; 
SELECT ROUND(5, -2) AS Round_2; 
SELECT ROUND(15, -3) AS Round_3; 
SELECT ROUND(160, -2) AS Round_4; 
SELECT ROUND(160, -3) AS Round_5;

As you can see output of Round Function:

For SELECT ROUND(5, -1), positive integer value 5 and negative length -2, round function rounds up the value to 10.

For SELECT ROUND(160, -3), positive integer value 160 and negative length -2, round function rounds up the value to 200.

Using Round function with decimal value and positive length

Following  T-SQL uses round function and round up to the nearest values for specified length number.

DECLARE @val DECIMAL(10, 2);
SET @val = 15.08;
SELECT ROUND(@val, 1) AS Round_1; 
SELECT ROUND(@val, 2) AS Round_2;
SELECT ROUND(@val, 3) AS Round_3;
SELECT ROUND(@val, 4) AS Round_4;

As you can see that with decimal values round up to the nearest value as per the given length.

Also Read..

SQL Mathematical Functions


SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 27 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.