When using T-SQL with SQL Server, we can format numbers using various methods, depending on our desired format.

Below are four functions that can be used to format a number to two decimal places in SQL Server.

The CAST() Function

The most obvious way to do it is to convert the number to a decimal type. Two functions that can do this for us is CAST() and CONVERT().

Here’s an example of using CAST():

SELECT CAST(275 AS DECIMAL(5, 2));

Result:

275.00

We can use this method even if the number is already a decimal value but with more decimal places.

One thing to remember though, is if you’re reducing the number of decimal places from a number with more than two decimal places, then you could end up with the second decimal place being rounded up:

SELECT CAST(275.4567 AS DECIMAL(5, 2));

Result:

275.46

The CONVERT() Function

Here, we use CONVERT() to do the same thing – convert the number to decimal:

SELECT CONVERT(DECIMAL(5, 2), 275);

Result:

275.00

The FORMAT() Function

Another way to format a number with two decimal places is to use the FORMAT() function:

SELECT FORMAT(275, 'N2');

Result:

275.00

This function actually converts the number to a string, so technically, the result is not a numeric type.

The N2 part is referred to as a format string. In this case, N is for number and 2 is for the number of decimal places (you can increase or decrease this as required).

The same technique can be used to reduce the decimal places to two, from a number with more decimal places:

SELECT FORMAT(275.4567, 'N2');

Result:

275.46

It’s been rounded up, just like when we converted the number in the other example.

Another way to do it is to use a custom format string. Custom format strings allow you to specify a format that might not be supported by a standard format string.

Here’s an example of using custom format strings:

SELECT 
    FORMAT(275, '###.##') AS "###.##",
    FORMAT(275, '000.00') AS "000.00",
    FORMAT(275.4567, '###.##') AS "###.##",
    FORMAT(275.4567, '000.00') AS "000.00";

Result:

+----------+----------+----------+----------+
| ###.##   | 000.00   | ###.##   | 000.00   |
|----------+----------+----------+----------|
| 275      | 275.00   | 275.46   | 275.46   |
+----------+----------+----------+----------+

We can see that there’s a difference between using # and 0 in the format string. The # format specifier suppresses any insignificant zeros while the 0 format specifier does not.

But perhaps more importantly (for the purposes of this article), the 0 format specifier allows you to add insignificant zeros if they aren’t present in the original number. Therefore, it can be used to add two decimal places to an integer (as seen in the above example).

The STR() Function

Another way to format a number to two decimal places is to use the STR() function:

SELECT STR(275, 6, 2);

Result:

275.00

This function returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.

The first argument is an expression of float data type with a decimal point.

The second argument is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

The third argument is the number of places to the right of the decimal point. This must be less than or equal to 16.