sql

SQL Server FORMAT() Function

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:

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.

sql format - date
Figure 1 SQL FORMAT Date

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

Download
You can download the full source code of this example here: SQL Server FORMAT() Function

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button