Home » Core Java » sql » SQL Server FORMAT() Function

About Mary Zheng

Avatar photo
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.

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

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments