In SQL Server, the FORMAT() function enables you to format date/time and number values as a formatted string by passing in a “format string” as the second argument (the first argument is the value that’s being formatted).

Here’s an example of this function in action:

FORMAT(@date, 'dd/MM/yyyy');

In this case the format string is dd/MM/yyyy.

This particular format string specifies that the @date value should be formatted with a two-digit day, two-digit month, and a four-digit year, in that order, and with forward slashes as the separators.

This would result in something like this:

21/05/2019

A format string is a string that contains one or more predefined format specifiers, which are single characters or groups of characters that define how the output should be formatted.

SQL Server only accepts format strings that are supported by the .NET Framework.

A format string can be a standard format string or a custom format string. Here’s how those work:

• A standard format string is a predefined format string. It contains a single format specifier which is interpreted as representing a certain, predefined format. Standard format strings are actually aliases for custom format strings. However, the actual custom format string used will often depend on the culture.
• A custom format string on the other hand, consists of one or more custom format specifiers that, in combination with each other, will define the format. This allows you a lot more flexibility with defining how the output should look.

Example 1 – Standard Format String

Here’s an example to demonstrate how standard format strings work.

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

Result:

+-----------+
| Result    |
|-----------|
| $1,234.00 |
+-----------+

In this example, C is the format string. In this case, it’s a standard numeric format string consisting of a single format specifier. This particular format specifier is used to represent a currency amount.

We could also get the same result by using a custom numeric format string.

Example 2 – Custom Format String

This example produces the same output as the previous example. The difference is that, in this example, I use a custom format string instead of a standard one.

SELECT FORMAT(1234, '$#,###.00') AS Result;

Result:

+-----------+
| Result    |
|-----------|
| $1,234.00 |
+-----------+

A custom format string allows you to create your own custom format. You can combine strings with format specifiers to build your own custom format.

In this example, I use the # format specifier, which is a placeholder for a digit. If a digit isn’t present, no digit appears in the resulting string.

I also use the 0 format specifier, which also is a placeholder for any digit. But in this case, if a digit isn’t present, a zero is used instead.

I also include the $, ,, and . literal strings in the format string. These are included in the output exactly as they are.

If we reduce the input number, we can see how the result is different between the 0 and # format specifiers, and how they compare to the C standard format specifier from the previous example:

SELECT 
  FORMAT(34, 'C') AS 'C',
  FORMAT(34, '$0,000.00') AS '0',
  FORMAT(34, '$#,###.00') AS '#';

Result:

+--------+-----------+--------+
| C      | 0         | #      |
|--------+-----------+--------|
| $34.00 | $0,034.00 | $34.00 |
+--------+-----------+--------+

As you might imagine, custom format strings provide a lot more flexibility over standard format strings.

However, there are many scenarios where standard format strings can be more powerful, especially when it comes to producing dynamic results that take into account the culture. More on culture soon.

Example 3 – Formatting Date and Time

Date/time values also offer the choice of standard or custom format strings. Here’s an example of a date/time value being formatted with a standard format string, as well as some custom format strings.

DECLARE @date datetime2(7);
SET @date = '2080-05-01 23:09:08.1234567';
SELECT 
  FORMAT(@date, 'd') AS 'd',
  FORMAT(@date, 'M/d/yyyy') AS 'M/d/yyyy',
  FORMAT(@date, 'dd/MM/yy') AS 'dd/MM/yy',
  FORMAT(@date, 'ddd, MMM dd, yy') AS 'ddd, MMM dd, yy',
  FORMAT(@date, 'dddd, dd MMMM yyyy') AS 'dddd, dd MMMM yyyy';

Result:

+----------+------------+------------+-------------------+------------------------+
| d        | M/d/yyyy   | dd/MM/yy   | ddd, MMM dd, yy   | dddd, dd MMMM yyyy     |
|----------+------------+------------+-------------------+------------------------|
| 5/1/2080 | 5/1/2080   | 01/05/80   | Wed, May 01, 80   | Wednesday, 01 May 2080 |
+----------+------------+------------+-------------------+------------------------+

The first one uses a standard format string, and the other four use custom format strings.

For a full list of available date and time format strings, see the following:

• List of the Standard Date/Time Format Strings
• List of the Custom Date/Time Format Strings

Example 4 – Culture

The results of format strings can sometimes depend on the culture being used. For example, in the US a short date format is represented as ‘M/d/yyyy’, but in Britain it’s represented as ‘dd/MM/yyyy’.

By default, the language of the current session is used to define the culture. However, the FORMAT() function allows you to override this.

The function’s syntax goes like this:

FORMAT ( value, format [, culture ] )

So it allows you to specify a culture as an optional argument.

Here’s an example of where different cultures can result in a single format string producing a number of different formats.

DECLARE @date datetime2(7);
SET @date = '2080-05-01 23:09:08.1234567';
SELECT 
  FORMAT(@date, 'd', 'en-us') AS 'US English',
  FORMAT(@date, 'd', 'en-gb') AS 'British',
  FORMAT(@date, 'd', 'de-de') AS 'German',
  FORMAT(@date, 'd', 'jp-jp') AS 'Japanese';

Result:

+--------------+------------+------------+------------+
| US English   | British    | German     | Japanese   |
|--------------+------------+------------+------------|
| 5/1/2080     | 01/05/2080 | 01.05.2080 | 05/01/2080 |
+--------------+------------+------------+------------+

In this case, all four countries use the d standard date and time format string. However, they all use a different culture argument. This produces a different result to suit each culture.

And it’s not just standard format strings that are influenced by culture. Culture can also influence the result of custom format strings. Here’s an example:

DECLARE @date datetime2(7);
SET @date = '2080-05-01 23:09:08.1234567';
SELECT 
  FORMAT(@date, 'dddd, dd MMMM', 'en-us') AS 'US English',
  FORMAT(@date, 'dddd, dd MMMM', 'de-de') AS 'German',
  FORMAT(@date, 'dddd, dd MMMM', 'vi') AS 'Vietnamese',
  FORMAT(@date, 'dddd, dd MMMM', 'sv') AS 'Swedish';

Result:

+-------------------+------------------+----------------------+----------------+
| US English        | German           | Vietnamese           | Swedish        |
|-------------------+------------------+----------------------+----------------|
| Wednesday, 01 May | Mittwoch, 01 Mai | Thứ Tư, 01 Tháng Năm | onsdag, 01 maj |
+-------------------+------------------+----------------------+----------------+

You also have the option of using the invariant culture (iv). The invariant culture is culture-insensitive. It’s associated with the English language but not with any country/region. For more info and examples, see How to Specify the Invariant Culture when using FORMAT() in SQL Server.