How to Format Numbers as Currency in SQL Server

Formatting numbers as currency in SQL Server adds clarity and professionalism to your data output. With the FORMAT() function, you can effortlessly achieve this task while maintaining precision and flexibility. 

The function offers you a certain level of control over how the currency should look, in terms of decimals or locale signs. So, let’s explore how you can master currency formatting in SQL Server with practical examples and insights.

How Do You Convert a Value to a Currency in SQL Server?

The FORMAT() function in SQL Server is a powerful tool for formatting numbers, dates, currencies, and more. It accepts three arguments: the number, the format (specified as ‘c’ for currency), and an optional culture argument. 

Basic Formatting: SQL Format Currency with Commas

Let’s kick off our journey with a basic example:

SELECT FORMAT(4567.89, 'C') AS Currency;
Output:
$4,567.89

In this example, we’ve formatted the number 4567.89 as currency using the ‘C’ format specifier, resulting in the dollar sign prefixed to the number and the comma added to the correct place.

SQL Format Currency: Decimal Places

Controlling the precision of the currency format is crucial for displaying data accurately. Let’s explore how to adjust decimal places.

SELECT FORMAT(1234, 'C0') AS Currency_No_Decimals,
       FORMAT(1234, 'C2') AS Currency_Two_Decimals;
Output:
$1,234
$1,234.00

By using precision specifiers like ‘C0’ or ‘C2’, we can specify the desired number of decimal places in our currency format.

SQL Format Currency: Without Dollar Sign

Currency formatting often varies based on locale. If you don’t want the default dollar sign, with the FORMAT() function, you can easily adapt currency formatting to different locales. Here’s how:

SELECT FORMAT(1234.56, 'C', 'fr-FR') AS Currency_France,
       FORMAT(1234.56, 'C', ' zh-cn') AS Currency_China;
Output:
1234.56 €
1234.56 ¥

In this example, we’ve formatted the number 1234.56 as currency for France and China, resulting in the appropriate currency symbols and formatting for each locale.

Frequently Asked Questions

How to declare currency data type in SQL Server?

Although SQL Server allows you to designate monetary values with a currency symbol, it’s important to note that the system doesn’t retain any currency-related information tied to the symbol. Instead, it solely stores the numeric value itself.

Is the format argument case sensitive?

No, the format argument is case-insensitive. That means whether you use ‘C’ or ‘c’, it will yield the same result.

Conclusion

With the FORMAT() function, you have the tools to tailor currency formatting to your specific needs, whether it’s controlling precision, adapting to different locales, or customizing currency symbols. If you have any questions or suggestions, we’re eager to hear from you. Your feedback drives our growth!