1. Rounding and Truncating numbers
When you are performing mathematical operations on numbers, your result may have 15 decimal places when you only want 2. In this lesson, we will look at rounding and truncating numbers to obtain the desired number of digits before and after the decimal.
2. Rounding numbers in T-SQL
The ROUND function lets you round numbers on either side of the decimal. It takes two required arguments, the number to be rounded, and length, the number of places the number should be rounded.
If the length specified is negative, the numbers on the left side of the decimal, that is the whole numbers are rounded. If the number is positive, the numbers on the right side of the decimal, the decimal numbers are rounded.
3. Rounding numbers in T-SQL
This example shows how to round the DurationSeconds column to return a whole number and also round to the first decimal. In the RoundToZero column, the number in DurationSeconds is rounded to whole numbers by specifying the second argument, as 0. As expected, 121-point-6 is rounded up to 122 and 170-point-3 is rounded down to 170.
In the RoundToOne column, we round the numbers to one decimal place by setting length to 1. 121-point-64 is rounded down to 121-point-6 and 170-point-39 is rounded up to 170-point-4.
4. Rounding on the left side of the decimal
Similarly, we can also round numbers on the left side of the decimal. Here we round the DurationSeconds column twice, again.
First, we round it to the nearest 10 by passing negative one as the second argument. Since 1 and 0 are the first digits to the left of the decimal in 121-point-6 and 170-point-3, these numbers are rounded down to 120 and 170, respectively. However, since 6 is the first digit to the left of the decimal in 336-point-0, it is rounded up to 340.
Then, we round DurationSeconds to the nearest 100 by passing negative two as the second argument. This time, since 2 and 3 are the second digits to the left of the decimal in 121-point-6 and 336-point-0, they are rounded down to 100 and 300, respectively. However, since 7 is the second digit to the left of the decimal in 170-point-3, it is rounded up to 200.
5. Truncating numbers
Sometimes, you just want to truncate values instead of rounding up or rounding down. For example, the result of truncating 17-point-85 to a whole number would be 17.
6. Truncating with ROUND()
Adding the third argument to the round function will truncate the value instead of rounding.
7. Truncating in T-SQL
This example shows the difference between Rounding and truncating. Because 0 is specified as the second argument in the ROUND function, all decimals will be rounded to the nearest whole number. When the value is rounded, it matters what the number is to the right of the decimal. When truncating, the numbers after the decimal are ignored. You can see from the example that the results of truncating and rounding are different since truncating returns the numbers to the left of the decimal no matter what.
8. Let's practice!
Time for some more practice.