I'm making a datatable, calling my data(JSON) by ajax, so i can't format my columns manually so instead, formatted directly to currency in SQL 2012
The problem is, when i try to use the Json with the concat in my datatable, the format disapeares. Is there a way to do this?
i tried:
FORMAT(bd.dbo.table.column, 'C', 'en-us') AS 'R1',
AND
concat('$',SUM(bd.dbo.table.column)) as R2
both work just fine in SQL but when i try them in Postman only the concat gives me the currency symbol.
edit**
my json gives me this
{"data":[{"col1":"0106","col2":"5","col3":4,"col4":3,"PENDIENTE":22029.328,"PENDIENTE2":"$22,029.33","PENDIENTE3":"$22029.3"}]}
this is my query
SELECT
database.dbo.table1.col1,
database.dbo.table1.col2,
database.dbo.table2.col3,
database.dbo.table1.col4,
sum(database.dbo.table2.colMoney*
database.dbo.table2.colMoney2) as PENDIENTE,
FORMAT(sum(database.dbo.table2.colMoney*
database.dbo.table2.colMoney2), 'C', 'en-us') AS PENDIENTE2,
concat('$',sum(database.dbo.table2.colMoney*
database.dbo.table2.colMoney2)) as PENDIENTE3
FROM
database.dbo.table1,
database.dbo.table2,
database.dbo.table3
WHERE
database.dbo.table1.ID =
database.dbo.table2.ID
AND
database.dbo.table2.colMoney > 0
GROUP BY
database.dbo.table1.col1,
database.dbo.table1.col2,
database.dbo.table2.col3,
database.dbo.table1.col4
the thing is in the table neither currency simbol nor format will be shown