SQL FORMAT Function Use and Examples

Overview

The FORMAT function is used to provide various output formats for values like numbers, dates, time, money.

Explanation

Syntax

FORMAT(expression, formatPattern, [Culture])

Parameters

expression – This is the value, number or expression that we want to set in a different format.
formatPattern – This is the format that is going to be used by the expression or value.
Culture– This is related to the format of each country and language. Microsoft stores information about date, numbers and currency formats used by different countries and languages which can be handled by this parameter. The Culture specifies the language and the country. For example, the culture English from USA is en-us. French from France is fr-fr. French from Belgium is fr-be. For a complete list of supported languages, refer to this list of supported languages.

Simple FORMAT Example

Below is a simple example of using FORMAT to display numbers with 3 decimals.

SELECT FORMAT(345,'###.000') as format

FORMAT Example with Decimals, Scientific Notation and Hexadecimal

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

SELECT 
   FORMAT(200,'N1', 'en-US') onedecimal, -- 1 decimal
   FORMAT(200,'N2', 'en-US') twodecimals, -- 2 decimals
   FORMAT(200,'E2', 'en-US') scientificNotation, --scientific notation
   FORMAT(200,'X',  'en-US') hexadecimal --hexadecimal

SQL Server decimal, scientific notation, hexadecimals

FORMAT Numeric Values Using Cultural Parameter

In USA, the decimal point is used and in other cultures like in Belgium, the comma is used for decimals. Here it is the example.

SELECT 
   FORMAT(200,'N2', 'en-US') [English US], 
   FORMAT(200,'N2', 'fr-be') [Dutch Belgium]

FORMAT Currency with Cultural Parameter

In SQL Server, it is possible to work with currencies of different countries. The following example shows the currencies used in USA, France and Russia.

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

SQL Server currencies from different countries

Using FORMAT for Custom Date Formats

The following example shows how to set a custom format for the current date. yyyy is for year, MM is for month, dd is for day, hh is for hour, mm for minutes, ss for seconds and tt for pm/am.

SELECT FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss tt') as format

FORMAT Dates with Cultural Parameter

The next example will show the date format in USA, France and Russia.

SELECT 
   FORMAT(GETDATE(),'d', 'en-US') [English US], 
   FORMAT(GETDATE(),'d', 'fr-fr') [French France],
   FORMAT(GETDATE(),'d', 'ru-ru') [Russian Russian]

custom dates and formats according to the country and language

FORMAT Dates from Table Column

Here is an example of how this could be used by formatting data from a column in a table.

SELECT 
   FORMAT(OrderDate,'d', 'en-US') [OrderDate English US], 
   FORMAT(TotalDue,'C', 'en-US') [TotalDue English US]
FROM [Sales].[SalesOrderHeader]

Here is a sample of the output.

Note: The FORMAT function uses Common Language Runtime (CLR) and there have been noticeable performance differences between other approaches (CONVERT Function, CAST Function, etc.) showing that FORMAT is much slower.

Additional Information

Resources

Community

Subscribe