In SQL Server, you can use the T-SQL FORMAT() function to return values such as numbers and dates as formatted strings.

You provide the value to be formatted, and you specify the format to use. The function accepts an optional argument that allows you to specify a culture to use when formatting the value.

Syntax

The syntax goes like this:

FORMAT ( value, format [, culture ] )

Where value is the value you want formatted, and format is a valid format string that specifies the desired format.

The optional culture argument can be used to specify a culture. If omitted, the language of the current session is used.

The FORMAT function is nondeterministic.

Example 1 – Format a Number

Here’s an example of formatting a number:

SELECT FORMAT(1, 'N') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1.00     |
+----------+

In this case, I used N as the format string. This is a standard numeric format specifier for outputting the value as a number. This particular format specifier results in the output being formatted with integral and decimal digits, group separators, and a decimal separator with optional negative sign. This format specifier is case-insensitive, so either N or n is fine.

Example 2 – Format to a Currency

Here’s an example of formatting a number as a currency:

SELECT FORMAT(1, 'C') AS Result;

Result:

+----------+
| Result   |
|----------|
| $1.00    |
+----------+

More Numeric Formats

There are many more format strings that can be used with the FORMAT() function. For example, there are format strings for percentages, fixed-point, exponential (scientific), hexadecimal, and more.

The numeric format strings are separated into standard and custom.

The following two articles list all of the numeric format strings, including examples:

• Standard Numeric Format Strings
• Custom Numeric Format Strings

Also see How to Format Numbers in SQL Server for more examples of formatting numbers.

Example 3 – Format a Date

Here’s an example of formatting a date:

SELECT 
  GETDATE() AS 'Unformatted Date',
  FORMAT( GETDATE(), 'D') AS 'Formatted Date';

Result:

+-------------------------+------------------------+
| Unformatted Date        | Formatted Date         |
|-------------------------+------------------------|
| 2019-05-08 06:16:55.613 | Wednesday, May 8, 2019 |
+-------------------------+------------------------+

In this case, I used D which specifies a long date pattern.

More Date Formats

There are many more format strings that can be used for formatting dates. As with the numeric format strings, date and time format strings are separated into standard and custom, so you can build your own custom formats, or you can rely on a standard one.

The following two articles list all of the date and time format strings, including examples:

• Standard Date and Time Format Strings
• Custom Date and Time Format Strings

Also see How to Format the Date & Time in SQL Server for more examples.

Example 4 – The Optional culture Argument

Here’s an example of using the culture argument to return a value in various currencies:

SELECT 
    FORMAT(1, 'C', 'fr-FR') AS 'France', 
    FORMAT(1, 'C', 'th-TH') AS 'Thailand', 
    FORMAT(1, 'C', 'ja-JP') AS 'Japan';

Result:

+----------+------------+---------+
| France   | Thailand   | Japan   |
|----------+------------+---------|
| 1,00 €   | ฿1.00      | ¥1      |
+----------+------------+---------+

The FORMAT() function accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server.

If you don’t provide this argument, the language of the current session is used.

For more examples, see How Language Settings can Affect your FORMAT() Results.

Example 5 – Invalid culture Argument

If you provide an invalid culture, you’ll get an error:

SELECT FORMAT(1, 'C', 'oop-SS!') AS 'Oops!';

Result:

The culture parameter 'oop-SS!' provided in the function call is not supported.

Example 6 – Invalid Format Value

However, for other errors, the function returns NULL. For example, here’s what happens if I provide an invalid value to be formatted:

SELECT FORMAT(GETDATE(), 'C') AS 'Result';

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

In this case, I was trying to format a date into a currency, and therefore the result was NULL.

Valid Data Types

FORMAT() relies on the presence of the .NET Framework Common Language Runtime (CLR), and uses the CLR formatting rules.

The following data types can be formatted with the FORMAT() function. This list contains the acceptable data types for the input string together with their .NET Framework mapping equivalent types.

Category	Type	.NET type
Numeric	bigint	Int64
Numeric	int	Int32
Numeric	smallint	Int16
Numeric	tinyint	Byte
Numeric	decimal	SqlDecimal
Numeric	numeric	SqlDecimal
Numeric	float	Double
Numeric	real	Single
Numeric	smallmoney	Decimal
Numeric	money	Decimal
Date and Time	date	DateTime
Date and Time	time	TimeSpan
Date and Time	datetime	DateTime
Date and Time	smalldatetime	DateTime
Date and Time	datetime2	DateTime
Date and Time	datetimeoffset	DateTimeOffset

Escaping Colons and Periods for the ‘time’ Data Type

When using FORMAT, colons and periods must be escaped (this adheres to the the CLR formatting rules). Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with a backslash when an input value (first parameter) is of the time data type.

Example:

SELECT 
  CAST('12:15' AS time) AS 'Unformatted Data',
  FORMAT(CAST('12:15' AS time), N'hh.mm') AS 'Unescaped',
  FORMAT(CAST('12:15' AS time), N'hh\.mm') AS 'Escaped';

Result:

+--------------------+-------------+-----------+
| Unformatted Data   | Unescaped   | Escaped   |
|--------------------+-------------+-----------|
| 12:15:00           | NULL        | 12.15     |
+--------------------+-------------+-----------+

So as expected, the unescaped string returns NULL.

As mentioned, this only applies to the time data type. If we change the input value to a different data type, we don’t need to escape it:

SELECT 
  CAST('12:15' AS datetime) AS 'Unformatted Data',
  FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Unescaped',
  FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Escaped';

Result:

+-------------------------+-------------+-----------+
| Unformatted Data        | Unescaped   | Escaped   |
|-------------------------+-------------+-----------|
| 1900-01-01 12:15:00.000 | 12.15       | 12.15     |
+-------------------------+-------------+-----------+

In this case, the input value is datetime, and therefore, the result is fine without escaping it.

You can also use the backslash to escape any other character you want included in the result string, that would otherwise be interpreted as a format specifier. Preceding a character with a backslash signifies that the following character is a character literal that should be included in the result string unchanged.

In a custom date and time format string, the d, f, F, g, h, H, K, m, M, s, t, y, z, :, or / characters are interpreted as custom format specifiers rather than as literal characters.

In a custom numeric format string, the #, 0, ., ,, %, and ‰ symbols are interpreted as format specifiers rather than as literal characters. The uppercase and lowercase E as well as the + and - symbols may also be interpreted as format specifiers, depending on their position within the format string.

If you need to include a backslash in the result string, escape it with another backslash.

Remoting

The FORMAT() function cannot be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR, could cause an error on the remote server.

When to Use the FORMAT() Function

Microsoft recommends that the FORMAT() function is used for locale-aware formatting of date/time and number values as strings, and that for general data type conversions, either the CAST() function or the CONVERT() function should be used instead.