At its simplest, we use FORMAT like this
DECLARE @a NUMERIC(19,5) = 1.23
PRINT @a -- returns 1.23000
PRINT FORMAT(@a,'n') -- returns 1.23
PRINT FORMAT(@a,'g') -- returns 1.23000
PRINT FORMAT(@a,'c') -- returns $1.23
PRINT FORMAT(@a,'#') -- returns 1
PRINT FORMAT(@a,'#.##') -- returns 1.23
SET @a = 12345.00
PRINT FORMAT(@a,'#') -- returns 12345
PRINT FORMAT(@a,'#,###') -- returns 12,345
PRINT FORMAT(@a,'$#,###') -- returns $12,345
Often in Dynamics I need a grid that formats the decimal places differently on different lines. (sometimes the unit price is two decimals, sometimes 5)
In order to get some varied test data in the TWO database, I change some amounts in the SOP10200 table. The results look like this:
;WITH CTE AS
(
SELECT TOP 5 *
from sop10200
order by sopnumbe
)
update CTE set DECPLCUR = 2, DECPLQTY = 5 , QUANTITY = 1.23456
select
sopnumbe,
xtndprce,
quantity,
h.decplqty,
h.decplcur,
format(XTNDPRCE,'$#,##0.' + REPLICATE('0',h.DECPLCUR - 1)) as FormattedAmt,
format(quantity,'#,##0.' + REPLICATE('0',h.decplqty - 1)) as FormattedQty
from sop10200 h
order by SOPNUMBE