I have a query string which returns a value that has several decimal places. I want to format this to a currency $123.45.
Here is the query:
SELECT COALESCE(SUM(SUBTOTAL),0)
FROM dbo.SALESORD_HDR
where ORDERDATE = datediff(d,0,getdate())
and STATUS NOT IN (3,6)
I want the result in a currency with 2 decimal places.
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.
I presume you are trying to change the result view in SQL Server Management Studio. If this the case what you need is 'Result to Grid' option. You can also use Ctrl + D to change the view to grid and 'Ctrl + T' to change it back to text.
Tip: You can also press Ctrl+1 to open the Format Cells dialog box. In the Format Cells dialog box, in the Category list, click Currency or Accounting. In the Symbol box, click the currency symbol that you want. Note: If you want to display a monetary value without a currency symbol, you can click None.
Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.
If you are looking for a "true" Currency format, similar to what can be achieved via the FORMAT function that started in SQL Server 2012, then you can achieve the exact same functionality via SQLCLR. You can either code the simple .ToString("C" [, optional culture info])
yourself, or you can download the SQL# library (which I wrote, but this function is in the Free version) and use it just like the T-SQL FORMAT
function.
For example:
SELECT SQL#.Math_FormatDecimal(123.456, N'C', N'en-us');
Output:
$123.46
SELECT SQL#.Math_FormatDecimal(123.456, N'C', N'fr-fr');
Output:
123,46 €
This approach works in SQL Server 2005 / 2008 / 2008 R2. And, if / when you do upgrade to a newer version of SQL Server, you have the option of easily switching to the native T-SQL function by doing nothing more than changing the name SQL#.Math_FormatDecimal
to be just FORMAT
.
Putting this into the context of the query from the original question:
SELECT SQL#.Math_FormatDecimal(COALESCE(SUM(SUBTOTAL),0), N'C', N'en-us') AS [Total]
FROM dbo.SALESORD_HDR
where ORDERDATE = datediff(d,0,getdate())
and STATUS NOT IN (3,6)
EDIT:
OR, since it seems that only en-us
format is desired, there is a short-cut that is just too easy: Converting from either the MONEY
or SMALLMONEY
datatypes using the CONVERT function has a "style" for en-us
minus the currency symbol, but that is easy enough to add:
SELECT '$' + CONVERT(VARCHAR(50),
CONVERT(MONEY, COALESCE(SUM(SUBTOTAL), 0)),
1) AS [Total]
FROM dbo.SALESORD_HDR
where ORDERDATE = datediff(d,0,getdate())
and STATUS NOT IN (3,6)
Since the source datatype of the SUBTOTAL
field is FLOAT
, it first needs to be converted to MONEY
and then converted to VARCHAR
. But, the optional "style" is one reason I prefer CONVERT
over CAST
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With