SQL Server FORMAT() Function

SQL Server FORMAT() function formats the specified value in the given format. 

FORMAT Function in SQL Server

The FORMAT function in SQL Server is used to format date/time and number values with locale-aware formatting.

The FORMAT function achieves various formatting requirements, showing dates in specific formats or formatting numeric values.

Syntax

SQL Server FORMAT() function syntax is:

FORMAT(value, format, culture)  

Parameter: 

FORMAT function accepts three parameters described below: 

• Value: It is the value to do formatting. It should be in support of the data type format. 
• Format: It is the required format in which we require the output.
• Culture: It is an optional parameter. By default, SQL Server uses the current session language for a default culture. We can provide a specific culture here, but the .Net framework should support it. We get an error message in case of invalid Culture

SQL Server FORMAT Function Example

Let's look at some examples of the FORMAT function in SQL Server. Learning SQL Server FORMAT() function with examples help in understanding the concept better.

Using FORMAT function to format a number example

In this example, we will format a number using FORMAT function.

Query:

SELECT FORMAT(25, 'N')

Output: 

Format in percentage using FORMAT function example

In this example, we will format a value to PERCENTAGE format. 

Query:

SELECT FORMAT(1, 'P', 'en-US')AS [PERCENTAGE IN US FORMAT], 
    FORMAT(1, 'P', 'en-IN') AS [PERCENTAGE IN INDIA FORMAT];

Output: 

Format in Date using FORMAT function example

In this example, we will format the date in the desired format.

Query:

DECLARE @d DATETIME = GETDATE();  
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'

Output: 

Format time in AM or PM format using FORMAT function example

In this example, we will format the current time with AM or PM. 

Query:

SELECT FORMAT(SYSDATETIME(), N'hh:mm tt');

Output: 

Format currency using FORMAT function example

In this example, we change the CURRENCY format. 
Query:

SELECT 
    FORMAT(1, 'C', 'in-IN') AS 'INDIA', 
    FORMAT(1, 'C', 'ch-CH') AS 'CHINA', 
    FORMAT(1, 'C', 'sw-SW') AS 'SWITZERLAND', 
    FORMAT(1, 'C', 'us-US') AS 'USA';

Output: 

sanjoy_62

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• SQL-Server