Format function in sqlserver


SqlServer, SqlServer Useful functions

Introduction


FORMAT FUNCTION

 

Some times it so happens that we have  many leading zeros after a decimal number .

For example I am getting a result of a query say value of closing stock  as 66000.000000 .

However I need to keep only 2 zeros after the decimal point and want the result as 66000.00 .

Here is a common trick of using the Format function.

Description


Here is a  General example which one can apply for formating 

DECLARE @n DECIMAL(9,6) =1.23;
SELECT @n
--> 1.230000
SELECT FORMAT(@n, '0.######')
--> 1.23

--Here  see HOW the FORMAT function is used. IIN the  2nd argument we are passing '0.####'   i.e,  a Zero  surrounded by single quote, a DECIMAL after ZERO and the FORMATING character #.   However the above result highlighted in yellow color is  truncating all the ZEROS in the decimal  portion.

If  we want to keep atleast 1 or 2 zeros the above  scenario can be modified as 

DECLARE @n DECIMAL(9,6) =1.23;

SELECT @n
--> 1.230000
SELECT FORMAT(@n, '0.00####')
--> 1.2300  

The Result would be as marked in red color

 

More Formating Options :  Using a custom format string (https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings