All my SQL queries return numbers with multiple decimal places. Queries similar to the below which returns an output of $37508.9845264100
. How can I reduce the decimal places to 2?
x
SELECT
SUM((dbo.Jobs.jmpProductionQuantity - dbo.SalesOrderLines.omlQuantityShipped) * dbo.SalesOrderLines.omlFullUnitPriceBase) AS "Total Value"
FROM
((dbo.Jobs
LEFT OUTER JOIN
dbo.SalesOrderJobLinks ON dbo.Jobs.jmpJobID = dbo.SalesOrderJobLinks.omjJobID)
LEFT OUTER JOIN
dbo.SalesOrderLines ON dbo.SalesOrderJobLinks.omjSalesOrderID = dbo.SalesOrderLines.omlSalesOrderID
AND dbo.SalesOrderJobLinks.omjSalesorderLineID = dbo.SalesOrderLines.omlSalesOrderLineID)
WHERE
dbo.SalesOrderLines.UOMLINVOICEDATE > '{%Last Month End Date YYYYMMDD%}'
AND dbo.SalesOrderLines.UOMLINVOICEDATE < '{%Next Month Start Date YYYYMMDD%}'
AND dbo.Jobs.jmpProductionComplete = 0
Advertisement
Answer
The FORMAT
function is probably the closest fit to what you want here:
SELECT
FORMAT(SUM((dbo.Jobs.jmpProductionQuantity -
dbo.SalesOrderLines.omlQuantityShipped) *
dbo.SalesOrderLines.omlFullUnitPriceBase), 'C2') AS "Total Value"
FROM
The mask C2
being used above means format as a currency with two decimal places of precision.