Reduce decimal places in SQL queries

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?

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

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.

Advertisement

Answer