SQL Server FORMAT Function
By: Daniel Calbimonte
The FORMAT function is used to provide various output formats for values like numbers, dates, time, money.
FORMAT(expression, formatPattern, [Culture])
- 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
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]
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]
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.
- Format SQL Server Dates with FORMAT Function
- Date and Time Conversions Using SQL Server
- Format numbers in SQL Server