asked on

SQL Server 2000 View - cannot format a column to 2 decimals

I am trying to format the InvoiceControllingGrossPriceUnit to 2 decimals but when I try putting this in FORMAT(field,"0.00") it comes back as format is not a recognized function. Is it something else in sql server 2000?

The code is attached. Thanks!

SELECT     TOP 100 PERCENT MIN(dbo.FS_ARInvoiceHeader.InvoiceDate) AS INV_Date, dbo.FS_ARInvoiceLine.LineItemNumber AS Inv_Item, 
                      MIN(dbo.FS_ARInvoiceHeader.CommissionCode) AS ComCode, dbo.FS_Item.ItemDescription, dbo.FS_ARInvoiceLine.ShipQuantity, 
                      dbo.FS_ARInvoiceLine.CommissionCode, dbo.FS_ARInvoiceLine.CustomerKey, dbo.FS_ARInvoiceHeader.CustomerID, 
                      dbo.FS_ARInvoiceLine.InvoiceControllingGrossUnitPrice, dbo.SA_Customer.CustomerName, dbo.MCC_StdPriceBookJoin.PB_FORMULA
FROM         dbo.FS_ARInvoiceLine INNER JOIN
                      dbo.FS_ARInvoiceHeader ON dbo.FS_ARInvoiceLine.ARInvoiceHeaderKey = dbo.FS_ARInvoiceHeader.ARInvoiceHeaderKey INNER JOIN
                      dbo.FS_Item ON dbo.FS_ARInvoiceLine.ItemKey = dbo.FS_Item.ItemKey INNER JOIN
                      dbo.SA_Customer ON dbo.FS_ARInvoiceLine.CustomerKey = dbo.SA_Customer.CustomerKey INNER JOIN
                      dbo.MCC_StdPriceBookJoin ON dbo.FS_ARInvoiceLine.LineItemNumber = dbo.MCC_StdPriceBookJoin.ItemNumber
GROUP BY dbo.FS_ARInvoiceLine.LineItemNumber, dbo.FS_Item.ItemClass4, dbo.FS_Item.ItemDescription, dbo.FS_ARInvoiceLine.ShipQuantity, 
                      dbo.FS_ARInvoiceLine.CommissionCode, dbo.FS_ARInvoiceLine.CustomerKey, dbo.FS_ARInvoiceHeader.CustomerID, 
                      dbo.FS_ARInvoiceLine.InvoiceControllingGrossUnitPrice, dbo.SA_Customer.CustomerName, dbo.MCC_StdPriceBookJoin.PB_FORMULA
HAVING      (dbo.FS_Item.ItemClass4 = 'F') AND (MIN(dbo.FS_ARInvoiceHeader.InvoiceDate) 
                      > CONVERT(DATETIME, '2011-08-31 00:00:00', 102))

Open in new window

SOLUTION