SQLTeam.com | Weblogs | Forums

Formatting Field to Number

#1

Hello.

I am trying to format a field to reflect 1,469 instead of 1469.

If I do Count(ln.Balance) I get 1469

Using Replace(Convert(Varchar,Convert(Money, Count(ln.Balance)),1),'.00',''), I get
Conversion failed when converting the varchar value '1,496' to data type int.

Where am I going awry? Thank you.

#2

It's ridiculous how difficult this is to do in SQL Server. The standard way, before FORMAT, is:

SELECT REPLACE(CONVERT(varchar(16), CAST(1469 AS money), 1), '.00', '')

If FORMAT is available to you, then:

SELECT FORMAT ( 1469, 'N0' )

#3

My apologies. I should have included my entire

Select
pr.ProductName,
ln.LoanType,
lt.LoanProductCode,
Total_Loans= Count(ln.Balance), --I want this number to include a comma if >1,000
Total_Balance='$'+Convert(Varchar,Convert(Money,SUM(ln.Balance)),1)

From Loan ln
Join LoanTypeRecord lt
On lt.LoanType=ln.LoanType
Join Product pr
On pr.FxpTypeNumber=ln.LoanType

Where
ln.Closed<>'-1'
AND ln.LoanType In ('2','3','14','15','17','18','19','20','24','26','28','33','38','39','40','41','65','75','79','96','100','101','102','106','111','112','113','114','115')
AND pr.ProductCategoryCode='2'
AND pr.ProductName<>'ZZAZ NEW AUTO VARIABLE'
--AND ln.Balance>'200000'

Group By
pr.ProductName,
ln.LoanType,
lt.LoanProductCode