In this SQL Server tutorial, you will learn how to format numbers to 2 decimal places in SQL Server.
You will use the different functions in SQL Server to format the number with 2 decimal places. Also, you will be introduced to the syntax of each function that you are going to use in this tutorial.
Format Number to 2 Decimal Places in SQL Server
If you want to format number to 2 decimal places in SQL Server, then you can do that using the different functions that exist in SQL Server. The functions are CAST(), CONVERT(), FORMAT() and STR().
Let’s see how to format numbers to 2 decimal places.
Format Number to 2 Decimal Places in SQL Server using CAST() Function
The CAST() function changes the data type of the given value to a different data type. If you provide an integer value to this function, it can change that value into a string type.
The syntax of the CAST() function is given below.
CAST(value_exp AS datatype);Where,
- CAST(): It is the function for casting one datatype into another datatype of a value.
- value_exp: It represents the value, expression, or column value whose datatype you want to change to another data type.
- datatype: Name the datatype to which you want to change the value.
For formatting the number to 2 decimal places, you will use the decimal datatype within the CAST() function.
Let’s say you have Products table which is shown below.
Now think you work with databases of ProductZone company in the USA and have to format the value within price columns to two decimal places for the product catalog.
For that, use the below query.
SELECT product_name, CAST(price AS decimal(10,2)) AS FormattedPrice
FROM products;
The output of the above query formatted the number within the price column with two decimal places as you can see in the above output. For example, Samsung Galaxy S21 product price is formatted from 799 to 799.00 with two decimal places.
Here CAST(price AS decimal(10,2)), converts each value of the price column to decimal with a precision of 10 (Where 10 represents the maximum number of digits in the number) and a scale of 2 (Where 2 represents the number of decimal places).
So that each price is formatted with two decimal places. This is you can format number to 2 decimal places in SQL Server.
Format Number to 2 Decimal Places in SQL Server using CONVERT() Function
The CONVERT() function also changes the data type of the given value to another datatype. Here you will see how to use this function to format numbers to two decimal places.
The syntax is given below.
CONVERT(datatype, value_exp);Where,
- CONVERT(): It is a function that converts the data type of the value to a different data type.
- datatype: Name the data type to which you want to change the given value.
- value_exp: The value, expression, or column that you want to change into another data type.
For example, let’s use the same example that you have used in the above sections. For that use the below query.
SELECT product_name, CONVERT(decimal(10,2),price) AS FormattedPrice
FROM products;
In the above output again you have formatted the price column value to two decimal places using the CONVERT() function. For example, the price of Amazon Echo Dot (3rd Gen) from 34 to 34.00.
Format Number to 2 Decimal Places in SQL Server using FORMAT()
The FORMAT() function in SQL Server formats the given value to a specified pattern. So here you are going to use this function to format the number to two decimal places.
The syntax is given below.
FORMAT(value, 'N2');Where,
- FORMAT(): It is the function that formats the value.
- value: The value or the column you want to format with two decimal places.
- ‘N2’: It is a format specifier which means format the number with 2 decimal places.
For example, Let’s you have a number 6578 and want to format this number with two decimal places.
For that use the below query.
SELECT FORMAT(6578,'N2');
Look how the FORMAT() function formatted the number from 6578 to 6,578.00. Also, it added the thousand separator to the number with two decimal places.
Let’s use the FORMAT() function in the Products table column, So for that use the below query.
SELECT product_name, FORMAT(price,'N2') AS FormattedPrice
FROM products;
After the execution of the above query, the FORMAT() function formats the price column with two decimal places. As you can see in the above output, for example, each price contains the double zero after the decimal point.
In the query part FORMAT(price, ‘N2’), where N means to format the number as a number and 2 means make sure that the number is displayed with two decimal places.
Format Number to 2 Decimal Places in SQL Server using STR() Function
The STR() function in SQL Server takes the numeric value and converts it into a string or character, and also allows you to specify the format for the value. So here you will use the STR() function to format the number to 2 decimal places.
The syntax is given below.
STR(numeric_value, length, decimal_places);Where,
- STR(): It is the function that formats the numeric value with specified decimal places.
- length: Specify the total length that you want after formatting or converting the number to string with padding and decimal places.
- decimal_places: The number of decimal places you want in the resultant string.
Let’s take a simple example to understand the workings of the STR() function.
SELECT STR(6783,10,2);
When you execute the above query, it converts the given 6783 numeric to a string of total length equal to 10 with two decimal places. If you look at the query output very closely, there are three leading spaces before the number___6783.00. These spaces are used to reach the total length of 10 characters.
Because including the number (6783), decimal point, and decimal places (00), it makes only 7 characters. So to fill the other 3 places, the STR() function used the space.
You can also use the STR() function in the table column. Suppose you have a Sales table with columns sale_id, product_name, sale_date, and sale_amount. The Sales table is shown below.
Now you working with a database of the online store in the USA and have to generate a monthly sales report that should contain the product name, seal date, and sale amount formatted with two decimal places.
You have access to the Sales table of the database as shown above. For that use the below query.
SELECT product_name,
FORMAT(sale_date, 'yyyy-MM') AS sale_month,
STR(sale_amount / 100.0, 10, 2) AS formatted_sale_amount
FROM sales;
In the above query, format each number within the sale_amount column with two decimal places that you can see in the above output.
Let’s see what happening in the query part STR(sale_amount / 100.0, 10, 2), the STR() function formats the sale_amount by dividing it by 100.0 and then converting it to a string with two decimal places.
For example, the product Fitbit Versa 3 Samartch is formatted from 165075 to 1650.75 in the above output of the query.
This is how you can format the number with two decimal places using the STR() function in SQL Server.
Conclusion
In this SQL Server tutorial, you learned how to format the number to 2 decimal places using the CAST(), CONVERT(), FORMAT(), and STR() functions with syntax in SQL Server.
You may like to read:
- How to Format Numbers with Leading Zeros in SQL Server?
- Format Number with Commas in SQL Server
- How to Format Number with Commas and Decimal in SQL Server?
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.