How The FORMAT Function SQL Server (T-SQL) Works?

Summary: In this tutorial, you will learn Format Function SQL and how to use them to the specified value in the given format.

Definition and Usage

Format function SQL is one of the String Function, which is used to format the specified value in the given format. SQL FORMAT() is an inbuilt function which is used for formatting a value with the specified format. The SQL FORMAT() function is used with date/time values and number values.
These function formats the value with the specified format (and an optional culture in SQL Server 2017).

FORMAT Function SQL Syntax

The basic syntax of the Format Function is as shown below:

In this syntax:

• Value: Required. Expression which is to be formatted. and specify a valid Expression of supporting Data Type.
• Format: Required. specify the pattern in which expression has to be formatted.
• Culture: Optional. This is an optional argument, it specifies the culture.

SQL Format Date Example

In this String Function illustration, first we declared a Datetime variable and assigned GETDATE() to it. Therefore, we are going to use the Format function to return the date in different formats.
I will suggest you to refer the Standard Date and Time Format Strings article to understand the string formats that we used in this example.

--Using FORMAT in SQL Server to Format Date
DECLARE @Vardate DATETIME = GETDATE() 
SELECT FORMAT(@Vardate, 'd', 'en-US' ) AS 'Result 1',  FORMAT(@Vardate, 'D', 'en-US' ) AS 'Result 2'
SELECT FORMAT(@Vardate, 'f', 'en-US' ) AS 'Result 3',  FORMAT(@Vardate, 'F', 'en-US' ) AS 'Result 4'
SELECT FORMAT(@Vardate, 'g', 'en-US' ) AS 'Result 5',  FORMAT(@Vardate, 'G', 'en-US' ) AS 'Result 6'
SELECT FORMAT(@Vardate, 'm', 'en-US' ) AS 'Result 7',  FORMAT(@Vardate, 'D', 'en-US' ) AS 'Result 8'
SELECT FORMAT(@Vardate, 'O', 'en-US' ) AS 'Result 9',  FORMAT(@Vardate, 'R', 'en-US' ) AS 'Result 10'
SELECT FORMAT(@Vardate, 's', 'en-US' ) AS 'Result 11', FORMAT(@Vardate, 'S', 'en-US' ) AS 'Result 12'
SELECT FORMAT(@Vardate, 't', 'en-US' ) AS 'Result 13', FORMAT(@Vardate, 'T', 'en-US' ) AS 'Result 14'
SELECT FORMAT(@Vardate, 'u', 'en-US' ) AS 'Result 15', FORMAT(@Vardate, 'U', 'en-US' ) AS 'Result 16'
SELECT FORMAT(@Vardate, 'Y', 'en-US' ) AS 'Result 17'

Output:

SQL Format Date using Culture

Therefore, Here we are going to use the format function third argument culture.
Here we can display the month name, or day name in the native language. such as Day name in Chinese, Japanese, Russian, Hindi, Korean etc.

--Using FORMAT in SQL Server to Format Date (Culture) 
DECLARE @Vardate DATETIME = GETDATE() 
SELECT FORMAT(@Vardate, 'dd', 'en-US' ) AS 'Result 1',  FORMAT(@Vardate, 'dddd', 'hi-IN' ) AS 'Result 2'
SELECT FORMAT(@Vardate, 'd', 'de-DE' ) AS 'Result 3',  FORMAT(@Vardate, 'dddd', 'ru-RU' ) AS 'Result 4'
SELECT FORMAT(@Vardate, 'M', 'en-US' ) AS 'Result 5',  FORMAT(@Vardate, 'MMMM', 'hi-IN' ) AS 'Result 6'
SELECT FORMAT(@Vardate, 'MM', 'de-DE' ) AS 'Result 7',  FORMAT(@Vardate, 'MMMM', 'ru-RU' ) AS 'Result 8'
SELECT FORMAT(@Vardate, 'yy', 'en-US' ) AS 'Result 9',  FORMAT(@Vardate, 'y', 'hi-IN' ) AS 'Result 10'
SELECT FORMAT(@Vardate, 'yyyy', 'de-DE' ) AS 'Result 11',  FORMAT(@Vardate, 'y', 'ru-RU' ) AS 'Result 12'

Output:

SQL Format Currency using Culture

In this example, we are going to use Format function in SQL Server to format the Currency values based on the specified culture.

--Using FORMAT in SQL Server to Format Currency Culture
DECLARE @Sales INT = 5255 
SELECT FORMAT(@Sales, 'c', 'ru-RU' ) AS 'Russian Currency'
SELECT FORMAT(@Sales, 'c', 'hi-IN' ) AS 'Indian Currency'
SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'USA Currency'
SELECT FORMAT(@Sales, 'c', 'de-DE' ) AS 'Indian Currency'

Output:

Therefore, as you can see in the above Format function example, we use the Format function to format the Currency. Furthermore, using this approach, you can display the countries Currency symbols before the Money or Value.

--Using FORMAT in SQL Server to Format Decimal Values in Currency
DECLARE @Sales DECIMAL(8, 4) = 5255.6577
SELECT FORMAT(@Sales, 'c' ) AS 'Result 1'
SELECT FORMAT(@Sales, 'c0' ) AS 'Result 2'
SELECT FORMAT(@Sales, 'c1' ) AS 'Result 3'
SELECT FORMAT(@Sales, 'c2' ) AS 'Result 4'
SELECT FORMAT(@Sales, 'c3' ) AS 'Result 5'
SELECT FORMAT(@Sales, 'c4' ) AS 'Result 6'

Output:

Custom Format Date

In this Illustration, we are going to define custom date time formats.
By this way, you are able to define the custom formats to display the date and time.

--Using FORMAT in SQL Server to Format Date in Custom Format
DECLARE @Vardate DATETIME = GETDATE() 
SELECT FORMAT(@Vardate, 'dd/mm/yyyy') AS 'date in dd/mm/yyyy Format'  
SELECT FORMAT(@Vardate, 'dd/mm/yy') AS 'date in dd/mm/yy Format' 
SELECT FORMAT(@Vardate, 'd/m/yy') AS 'date in d/m/yy Format' 
SELECT FORMAT(@Vardate, 'mm/dd/yyyy') AS 'date in mm/dd/yyyy Format' 
SELECT FORMAT(@Vardate, 'MMMM dddd yyyy hh:mm:ss:mmmm') AS 'date in MMMM dddd yyyy hh:mm:ss:mmmm Format' 
SELECT FORMAT(@Vardate, 'MMMM yyyy, dddd hh:mm:ss:mmmm') AS 'date in MMMM yyyy, dddd hh:mm:ss:mmmm Format'

Output:

Format Numbers using Culture

Here,we are going to use the Format function to format Numbers. Furthermore, the Culture specify the number formats. such as, few countries separate 100s, and some others countries separate 1000s etc.

--Using FORMAT in SQL Server to Format Numbers
DECLARE @Number DECIMAL(11, 4) = 1763775.8919 
SELECT FORMAT(@Number, 'N', 'en-US') AS 'Result 1'
SELECT FORMAT(@Number, 'N', 'en-IN' ) AS 'Result 2'
SELECT FORMAT(@Number, 'N', 'ru-RU') AS 'Result 3'
SELECT FORMAT(@Number, 'N', 'fr-FR' ) AS 'Result 4'
SELECT FORMAT(@Number, 'N', 'de-DE') AS 'Result 5'
SELECT FORMAT(@Number, 'N', 'zh-CN') AS 'Result 6'

Output:

SQL Server Format Numbers

Here, In this example, we are going to format the numbers. Therefore, to format the numbers, you can use the # symbols. Or otherwise you can also use the 0 to specify the number of decimal values.

--Using FORMAT in SQL Server to Format Numbers
DECLARE @Number DECIMAL(10,4) = 947774.1295 
DECLARE @Num INT = 945777534 
SELECT FORMAT(@Number, '#') AS 'Result 1'
SELECT FORMAT(@Number, '#,###' ) AS 'Result 2'
SELECT FORMAT(@Number, '#.00') AS 'Result 3'
SELECT FORMAT(@Number, '#,###.000' ) AS 'Result 4'
SELECT FORMAT(@Num, '###-##-####') AS 'Result 5'

Output:

Conclusion

In this tutorial, you have learned the SQL Format Function and you can see in the above different example that how to use them to the specified value in the given format.