SQL Server FORMAT() Function

The SQL Server (Transact-SQL) FORMAT() function returns a value formatted with the specified format and optional culture. This function is generally used for locale-aware formatting of date/time and number values as strings. For general data type conversions, CAST() or CONVERT() function can be used.

Syntax

FORMAT(value, format, culture)

Parameters

`value`	`Required.` Specify the value of a supported data type to format.
`format`	`Required.` Specify the format in which the output is required.
`culture`	`Optional.` Specify the culture. If omitted, the language of the current session is used. It accepts any culture supported by the .NET Framework as an argument. It is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, an error is raised.

Return Value

Returns the value formatted with the specified format.

Example: Format Decimals, Scientific Notation and Hexadecimal

The example below shows how to work with different number of decimals, scientific notation and hexadecimal.

--1 decimal format
SELECT FORMAT(12345.6789, 'N1', 'en-US');
Result: '12,345.7'

--2 decimal format
SELECT FORMAT(12345.6789, 'N2', 'en-US');
Result: '12,345.68'

--scientific notation format
SELECT FORMAT(12345.6789, 'E2', 'en-US');
Result: '1.23E+004'

--hexadecimal format
SELECT FORMAT(111, 'X', 'en-US');
Result: '6F'

Example: Format Currency with Cultural Parameter

The example below shows how to use this function to work with currencies of different countries.

SELECT FORMAT(100,'C', 'en-US') [English US], 
       FORMAT(100,'C', 'fr-fr') [French France],
       FORMAT(100,'C', 'ru-ru') [Russian Russian];

Result:  
English US   French France   Russian Russian                      
-----------  --------------  ----------------
$100.00      100,00 €        100,00 ₽

Example: Format Dates with Cultural Parameter

The example below shows how to use this function to format dates in a given culture.

DECLARE @d DATE = '11/22/2020';
SELECT FORMAT( @d, 'd', 'en-US' ) [US English],  
       FORMAT( @d, 'd', 'en-gb' ) [Great Britain English], 
       FORMAT( @d, 'd', 'de-de' ) [German]; 

Result:  
US English   Great Britain English  German      
----------   ---------------------  ---------- 
11/22/2020   22/11/2020             22.11.2020

Using 'D' as format argument in the above example will give the following result:

DECLARE @d DATE = '11/22/2020';
SELECT FORMAT( @d, 'D', 'en-US' ) [US English],  
       FORMAT( @d, 'D', 'en-gb' ) [Great Britain English], 
       FORMAT( @d, 'D', 'de-de' ) [German];  
      
Result:  
US English                   Great Britain English   German                      
---------------------------  ----------------------  --------------------------
Sunday, November 22, 2020    22 November 2020        Sonntag, 22. November 2020

❮ SQL Server Functions