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:
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:
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.