SQL Server 2008：如何将输出格式化为货币

I have a query string which returns a value that has several decimal places. I want to format this to a currency $123.45.

我有一个查询字符串，它返回一个有几个小数位的值。我想将其格式化为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.

我希望结果以2位小数的货币。

1 个解决方案

#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.

如果您正在寻找“真正的”货币格式，类似于通过SQL Server 2012中启动的FORMAT功能可以实现的格式，那么您可以通过SQLCLR实现完全相同的功能。您可以自己编写简单的.ToString（“C”[，可选的文化信息]），也可以下载SQL＃库（我写的，但这个函数是免费版本）并像T一样使用它-SQL FORMAT函数。

For example:

例如：

SELECT SQL#.Math_FormatDecimal(123.456, N'C', N'en-us');

Output:

输出：

$123.46

$ 123.46

SELECT SQL#.Math_FormatDecimal(123.456, N'C', N'fr-fr');

Output:

输出：

123,46 €

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.

此方法适用于SQL Server 2005/2008/2008 R2。而且，如果/当您升级到较新版本的SQL Server时，您可以选择通过将SQL＃.Math_FormatDecimal更改为FORMAT来轻松切换到本机T-SQL函数。

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:

或者，因为似乎只需要en-us格式，所以有一个快捷方式太简单了：使用CONVERT函数从MONEY或SMALLMONEY数据类型转换为en-us减去货币的“样式”符号，但这很容易添加：

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.

由于SUBTOTAL字段的源数据类型是FLOAT，因此首先需要将其转换为MONEY，然后转换为VARCHAR。但是，可选的“样式”是我更喜欢CONVERT而不是CAST的一个原因。