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.
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' )
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