SQL Reference - FORMAT

FORMAT( value, format [, culture ] )

value: This is the first parameter of the FORMAT function. It signifies the actual value that is to be formatted. It could be of different types such as date, number or time.
format: This is a pattern representing the format to which the value parameter is to be converted. This pattern is specified as a string. For numeral types, it could be patterns such as ‘C’ for currency format. For date and time types, it could be patterns like ‘dd/MM/yyyy’ for date format.
culture: This is an optional parameter of the FORMAT function. It is a string that represents a culture setting, which determines the formatting rules to be applied. If this parameter is not specified, the function will use the default culture of the environment where the query is running. For example, ‘en-US’ would format the value according to the United States culture norms.

Example

SELECT FORMAT (getdate(), 'MM/dd/yyyy') as Date;

Output

-------------------
|       Date      |
-------------------
|    07/20/2022   |
-------------------

Explanation

The FORMAT function is used in SQL Server to format a date or number according to the specified format. In the example given, the current date is formatted to display in the ‘MM/dd/yyyy’ format.

FORMAT(X, D, [locale])

x: This is the number to be formatted. X can be a BIGINT or DECIMAL value that needs to be converted into a string.
d: This refers to the number of decimal positions. It allows to set the number of decimal points you want to have in your number.
locale: This is an optional parameter. It specifies the locale that will be used to format the number. If no locale is specified, the default is ‘en_US’.

Example

SELECT FORMAT(1234567.89, 2, 'de_DE') AS 'Formatted number';

Output

Formatted number
1.234.567,89

Explanation

In the given SQL query, the FORMAT() function is used to format a number to a format specified by a locale. The number 1234567.89 is formatted to have two decimal places and use the ‘de_DE’ (German) locale, which uses period as thousand separator and comma as decimal separator. The output of the query is ‘1.234.567,89’.