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

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

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.