Format a Number with Commas But Without Decimals in SQL Server 2008 R2

Format a number with commas but without decimals in SQL Server 2008 R2?

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT  left(@val, len(@val) - 3)

This also works with digits after the decimal point:

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT  left(@val, len(@val) - 3)

Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.

How do I format a number with commas in T-SQL?

In SQL Server 2012 and higher, this will format a number with commas:

select format([Number], 'N0')

You can also change 0 to the number of decimal places you want.

SQL Server format decimal places with commas

without considering this to be a good idea...

select dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 4), 1234.1234567), 1))

Function

-- Author:      bummi
-- Create date: 20121106
CREATE FUNCTION F_AddThousandSeparators(@NumStr varchar(50)) 
RETURNS Varchar(50)
AS
BEGIN
declare @OutStr varchar(50)
declare @i int
declare @run int

Select @i=CHARINDEX('.',@NumStr)
if @i=0 
    begin
    set @i=LEN(@NumStr)
    Set @Outstr=''
    end
else
    begin   
     Set @Outstr=SUBSTRING(@NUmStr,@i,50)
     Set @i=@i -1
    end 

Set @run=0

While @i>0
    begin
      if @Run=3
        begin
          Set @Outstr=','+@Outstr
          Set @run=0
        end
      Set @Outstr=SUBSTRING(@NumStr,@i,1) +@Outstr  
      Set @i=@i-1
      Set @run=@run + 1     
    end

    RETURN @OutStr

END
GO

SQL Server 2008 thousands separator for a column

Try this way:

SELECT REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, TotalArea), 1), '.00', '') 
FROM table

or

SELECT CAST(CONVERT(VARCHAR, CAST(123456 AS MONEY), 1) AS VARCHAR)
FROM table

A more concise way to format COUNT result with thousands separator?

Not the most elegant, but you can remove the trailing .00 with replace.

SELECT REPLACE(CONVERT(VARCHAR, CAST(COUNT([id]) AS MONEY), 1), '.00', '')
FROM tbl

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

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.

---

Related Topics