{"id":3407,"date":"2018-11-01T18:27:23","date_gmt":"2018-11-02T01:27:23","guid":{"rendered":"https:\/\/database.guide\/?p=3407"},"modified":"2019-11-19T22:57:08","modified_gmt":"2019-11-20T05:57:08","slug":"round-examples-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/round-examples-in-sql-server\/","title":{"rendered":"ROUND() Examples in SQL Server"},"content":{"rendered":"

In\u00a0SQL Server<\/a>, the T-SQL<\/a> ROUND()<\/code> function allows you to round a number to a specified length or precision.<\/p>\n

You provide the number as an argument, as well as the length you’d like the number to be rounded to. The function also accepts an optional third argument that allows you to specify whether the number is rounded or truncated.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

ROUND ( numeric_expression , length [ ,function ] )  \r\n<\/pre>\n
Where the arguments have the following definitions: <\/p>\n
\n
numeric_expression<\/var><\/dt>\n
Is an expression of the exact numeric or approximate numeric data type category, except for the bit<\/strong> data type.<\/dd>\n
length<\/var><\/dt>\n
Is the precision to which numeric_expression<\/var> is to be rounded. This argument must be an expression of type tinyint<\/strong>, smallint<\/strong>, or int<\/strong>. When it’s a positive number, numeric_expression<\/var> is rounded to the number of decimal positions specified by this argument. When it’s a negative number, numeric_expression<\/var> is rounded on the left side of the decimal point, as specified by this argument.<\/dd>\n
function<\/var><\/dt>\n
Is the type of operation to perform. This must be tinyint<\/strong>, smallint<\/strong>, or int<\/strong>. When this argument is omitted or has a value of 0 (default), numeric_expression<\/var> is rounded. When a value other than 0 is specified, numeric_expression<\/var> is truncated.<\/dd>\n<\/dl>\n
Example 1 – Basic Usage<\/h2>\n
Here’s a basic example to demonstrate how this function works.<\/p>\n
SELECT ROUND(1.49, 1) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 1.50     |\r\n+----------+\r\n<\/pre>\n
In this case the number is rounded up.<\/p>\n
Example 2 – Zero Decimal Places<\/h2>\n
If we set the second argument to zero, here’s what happens:<\/p>\n
SELECT ROUND(1.49, 0) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 1.00     |\r\n+----------+\r\n<\/pre>\n
This is because we specified zero fractional parts with which to round the number.<\/p>\n
And here’s what happens if I change the initial number to 1.50:<\/p>\n
SELECT ROUND(1.50, 0) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 2.00     |\r\n+----------+\r\n<\/pre>\n
Example 3 – More Decimal Places<\/h2>\n
Here’s an example where the number to be rounded contains more decimal places.<\/p>\n
SELECT ROUND(1.234, 2) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 1.230    |\r\n+----------+\r\n<\/pre>\n
And here’s what happens when I increase the last digit to 5:<\/p>\n
SELECT ROUND(1.235, 2) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 1.240    |\r\n+----------+\r\n<\/pre>\n
Of course, we could use many more decimal places. For example, we could trim a lot of decimal places down.<\/p>\n
SELECT ROUND(1.23456789123456789, 8) Result;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+\r\n| Result              |\r\n|---------------------|\r\n| 1.23456789000000000 |\r\n+---------------------+\r\n<\/pre>\n
Example 4 – Truncation (i.e. using a third argument)<\/h2>\n
As mentioned, we can provide a third argument to specify whether the result is truncated or just rounded. The previous examples are all rounded because we didn’t specify a third argument. When we omit the third argument, a value of 0 is used (which means to round the result). If we provide a value other than 0, the result is truncated.<\/p>\n
SELECT \r\n  ROUND(1.236, 2) 'Rounded (by default)',\r\n  ROUND(1.236, 2, 0) 'Rounded (explicitly)',\r\n  ROUND(1.236, 2, 1) 'Truncated';\r\n<\/pre>\n
Result:<\/p>\n
+------------------------+------------------------+-------------+\r\n| Rounded (by default)   | Rounded (explicitly)   | Truncated   |\r\n|------------------------+------------------------+-------------|\r\n| 1.240                  | 1.240                  | 1.230       |\r\n+------------------------+------------------------+-------------+\r\n<\/pre>\n
Example 5 – Using a Negative Value for the Second Argument<\/h2>\n
Here’s how we can use a negative value on the second argument to cause parts of the non-fractional part to be rounded to zero.<\/p>\n
SELECT ROUND(123.456, -1) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 120.000  |\r\n+----------+\r\n<\/pre>\n
And here’s how it gets rounded if we increase the 3<\/code> to 5<\/code> or more.<\/p>\n
SELECT ROUND(125.456, -1) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 130.000  |\r\n+----------+\r\n<\/pre>\n
You can do this even if the number contains no fractional part.<\/p>\n
SELECT ROUND(125, -1) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 130      |\r\n+----------+\r\n<\/pre>\n
Example 6 – Rounding Negative Numbers vs Positive Numbers<\/h2>\n
As seen in the previous examples, when using positive numbers, a value with a fractional part of .5 or greater is rounded up to the next integer.<\/p>\n
However, when using negative numbers, such values are rounded down<\/em>.<\/p>\n
SELECT \r\n  ROUND(1.50, 0) Positive,\r\n  ROUND(-1.50, 0) Negative;\r\n<\/pre>\n
Result:<\/p>\n
+------------+------------+\r\n| Positive   | Negative   |\r\n|------------+------------|\r\n| 2.00       | -2.00      |\r\n+------------+------------+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
In\u00a0SQL Server, the T-SQL ROUND() function allows you to round a number to a specified length or precision. You provide the number as an argument, as well as the length you’d like the number to be rounded to. The function also accepts an optional third argument that allows you to specify whether the number is … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,70,77,61],"class_list":["post-3407","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mathematical-functions","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/3407","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=3407"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/3407\/revisions"}],"predecessor-version":[{"id":3412,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/3407\/revisions\/3412"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=3407"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=3407"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=3407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}