In SQL Server, you can use the T-SQL FORMAT() function to format a number as a currency.

The FORMAT() function allows you to format numbers, dates, currencies, etc. It accepts three arguments; the number, the format, and an optional “culture” argument. This article specifically deals with using the format argument to specify a currency.

Example 1 – Basic Usage

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

SELECT FORMAT(1234, 'C') Result;

Result:

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

In this case, I used C as the second argument. This is the standard numeric format string for Currency.

Example 2 – Case Sensitivity

This argument is case-insensitive, so either C or c returns the same result:

SELECT FORMAT(1234, 'c') Result;

Result:

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

Example 3 – Decimal Places

Here’s another example, this time using C0 to specify that we don’t want any decimal places:

SELECT FORMAT(1234, 'C0') Result;

Result:

+----------+
| Result   |
|----------|
| $1,234   |
+----------+

This is referred to as a “precision specifier”, probably because it allows you to specify the precision with which the result is displayed.

You can also increase the number of decimal places too:

SELECT FORMAT(1234, 'C4') Result;

Result:

+-------------+
| Result      |
|-------------|
| $1,234.0000 |
+-------------+

Example 4 – Locale

The previous examples all resulted in a dollar sign being prefixed to the number. This is fine if the desired currency is in dollars, but what if you need to display it in a different currency?

You can use a third argument to specify the locale. This will automatically result in the appropriate currency symbol being used for that locale:

SELECT FORMAT(1234, 'C', 'fr-FR') Result;

Result:

+------------+
| Result     |
|------------|
| 1 234,00 € |
+------------+

Here are some more locales:

SELECT 
  FORMAT(1234, 'C', 'fr-FR') 'France',
  FORMAT(1234, 'C', 'zh-cn') 'China',
  FORMAT(1234, 'C', 'th-TH') 'Thailand',
  FORMAT(1234, 'C', 'de-DE') 'Germany';

Result:

+------------+-----------+------------+------------+
| France     | China     | Thailand   | Germany    |
|------------+-----------+------------+------------|
| 1 234,00 € | ¥1,234.00 | ฿1,234.00  | 1.234,00 € |
+------------+-----------+------------+------------+

If the culture argument isn’t specified, the language of the current session is used.

Here’s how to find the language of the current session, and here’s how to set it.

Note that the current language will usually be the same as the user’s default language, but this might not be the case if the user has changed the current language using SET LANGUAGE. In any case, you can also find out what the default language is.

As you might imagine, you could get quite different results depending on your current language or the value of any “culture” argument. See How Language Settings can Affect your FORMAT() Results for more examples.