1. Introduction
SQL Server provides a format function that returns a formatted value based on the specified format. It is very useful as different countries have different formats for the number, date, and currency. Here is the syntax for the Format function:
FORMAT Syntax
FORMAT( value, format [, culture ] )
It returns a formatted string and has three arguments:
- value – required, the value to be formatted, can be either numeric or date types.
- format – required, the format pattern. e.g. ‘N’ for number, ‘C’ for currency, ‘D’ for date. Click here to see a list of valid format patterns.
- culture – optional, the language culture value . If not set, then default to the value of the SET LANGUAGE statement. Click here to see a list of valid cultures.
2. Technologies Used
The example code in this article was built and run using:
- SQL
- SQL Server here
3. Format Number
In this step, I will show how to format a number with the FORMAT function.
3.1 Format Pattern N
Open a web browser and navigate here. Then, enter the SQL command:
FORMAT N
SELECT FORMAT(123456789.34, 'N');
Click the “RUN SQL” button and you should see the following output:
123,456,789.34
The format pattern N is for number, N1 is for number format with 1 decimal. The following command returns the number with a one decimal point.
FORMAT N1
SELECT FORMAT(123456789.37, 'N1')
Click the “Run SQL” button and you should see the following output:
123,456,789.4
3.2 Format Pattern P
In this step, I will format a number with percentage format with the P pattern.
I will continue by using the online tool and enter the following SQL command:
FORMAT P
SELECT FORMAT(.37, 'P');
Click “Run SQL“, you should see the following output:
37.00%
3.3 Format Pattern C
In this step, I will format money with the C pattern. Enter the following command:
FORMAT C
SELECT FORMAT (1234, 'C');
Click “Run SQL“, you should see the following output:
$1,234.00
3.4 Format with Custom Pattern
In this step, I will format a number based on the customized pattern – ##-##-#####.##.
FORMAT Custom Pattern
SELECT FORMAT(123456789.34, '##-##-#####.##');
Click “Run SQL”, you should see the following output:
12-34-56789.34
4. Format Date
In this step, I will show how to format a date with the FORMAT function.
4.1 Format Pattern d & D
Continue with the online SQL tool, enter the following command:
FORMAT D
DECLARE @d DATETIME = '1/23/2022';
SELECT FORMAT (@d, 'd', 'en-US') AS 'US English Result',
FORMAT (@d, 'd', 'no') AS 'Norwegian Result',
FORMAT (@d, 'd', 'zu') AS 'Zulu Result',
FORMAT (@d, 'D', 'zu') AS 'Zulu Result long',
FORMAT(@d, 'd', 'zh') AS 'Chinese Result',
FORMAT(@d, 'D', 'zh') AS 'Chinese Result long'Click the “RUN SQL” button and you should see the following output.
4.2 Format with Custom Pattern
Continue with the online SQL tool, enter the following command:
FORMAT Custom Pattern
SELECT FORMAT (getdate(), 'dd/MM/yyyy');
Click “RUN SQL“, you should see the following output:
23/01/2022
5. Summary
In this example, I explained the SQL FORMAT function and showed a few examples to format a number and date types. Click here for more detailed examples.
6. Download the Source Code
You can download the full source code of this example here: SQL Server FORMAT() Function
