SQL Server 2008: how to format the output as a currency

Question

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.

Levi

Do you mean a "real" currency, one that can handle formatting for various cultures? Similar to the FORMAT function that started in SQL Server 2012 (msdn.microsoft.com/en-us/library/hh213505.aspx)? – Metacarpal

No, just a single culture (en-us) for New Zealand Dollars $#,###.00 – Levi

Ok. I still added an answer that would account for the full formatting (i.e. the currency symbol, placement of the symbol, thousands separator, and decimal point). But to be clear, you do want the thousands separator (as shown in the example in your comment), right? – Metacarpal

It would be good / better. – Levi

Answer 1

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.

Metacarpal

OK, so how do I write it to fit the code I have? if i put my query in where your fixed value of 123.456 is, it fails and says cannot nest a query inside. – Levi

@Bevan: it is just a function like COALESCE, SUM, etc. I will update my answer to use it in the context of your query. – Metacarpal

@Levi : answer has been updated with the actual usage. – Metacarpal

@Levi : I might have just found an even simpler answer that does what you want. Is the SUBTOTAL field a MONEY or SMALLMONEY datatype? – Metacarpal

Hmmm. did I get it wrong, maybe...Cannot find either column "SQL#" or the user-defined function or aggregate "SQL#.Math_FormatDecimal", or the name is ambiguous. – Levi

@Bevan: did you download SQL# from the website and install it? Either way, that might be a moot point now. I will adjust the new query to reflect a source datatype of FLOAT. (though next question would be why using FLOAT to store money values ;-)) – Metacarpal

he he: ask MYOB (it's their database structure!) – Levi

@Bevan, take a look at my update. I am glad that I thought to check to see if the CONVERT function had a format-specifier for MONEY ;-). And I was only asking about FLOAT as it is an imprecise datatype, though I think many moons ago, when I was first learning database, I did the same thing. – Metacarpal

@Rajesh : no problem. And if you didn't see the final update, I did find an even easier method for SQL 2005 / 2008, but only for cultures that use comma as a thousands separator and a period as the decimal point. – Metacarpal

Answer 2

Here's a suggestion of the syntax to use to future-proof the search query.

select format(123.56789,'C2','en-US')  --$123.57 ;
select format(123.56789,'C3','en-US') --$123.568;
select format(123.56789,'C0','en-US') --$124

Pall

The format function is not available in SQL Server 2008. – Pyroxenite