SQL Server 2008：如何将输出格式化为货币 [英] SQL Server 2008: how to format the output as a currency

问题描述

我有一个查询字符串，返回一个有小数位的值。我想把这个格式设置为123.45美元。

以下是查询：

  SELECT COALESCE（SUM（SUBTOTAL），0）
 FROM dbo.SALESORD_HDR 
 where ORDERDATE = datediff（d，0，getdate（））
和STATUS NOT IN（3， 6）
  

我想要一个2位小数的结果。如果你正在寻找一个真正的货币格式，类似于可以通过 .ToString（C[，可选文化信息]），也可以下载 FORMAT


$ b $ p $ SQL＃.Math_FormatDecimal（123.456，N'C'，N'en-us'）;


输出：

$ 123.46

  SELECT SQL＃.Math_FormatDecimal（123.456，N'C'，N 'FR-FR'）; 
  

输出：

123,46€

这种方法适用于SQL Server 2005/2008/2008 R2。而且，如果/当您升级到较新版本的SQL Server时，您可以通过更改名称 SQL＃.Math_FormatDecimal 来轻松切换到本机T-SQL函数。 code>只是 FORMAT 。





把这个放到原始问题的查询语境中：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'我们'）AS [总计]
FROM dbo.SALESORD_HDR
其中ORDERDATE = datediff（d，0，getdate（））
和STATUS NOT IN（3,6）


编辑：

<因为它似乎只有 en-us 格式是需要的，所以有一个简单的方法很简单：从 MONEY code>或 SMALLMONEY 数据类型使用 CONVERT 函数对于 en-us 减去货币符号，但是很容易添加：
$ b $ pre $ SELECT $ CONVERT（VARCHAR（50），
CONVERT（MONEY，COALESCE（SUM（SUBTOTAL），0）），
1）AS [总计]
FROM dbo.SALESORD_HDR
其中ORDERDATE = datediff（d，0，getdate（） ）
和STATUS NOT IN（3,6）


由于 SUBTOTAL 字段是 FLOAT ，它首先需要转换为 MONEY 然后转换为 VARCHAR 。但是，可选的风格是我更喜欢 CONVERT CAST 的原因之一。

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.

解决方案

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.

这篇关于SQL Server 2008：如何将输出格式化为货币的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！