[dba-SQLServer] SQL Server 2008 Display Formatting Problem

I'm working my way through the exercises and examples in "Murach's SQL
Server 2008 for Developer's" book when I come to Figure 4-15 (page 151)
which has a very interesting example of how you can combine data from the
same table using the UNION keyword.  (Here's the code for that example.)

 

USE AP

GO

      SELECT InvoiceNumber, VendorName, '33% Payment' AS PaymentType,

             InvoiceTotal AS [Invoice Total], (InvoiceTotal * 0.333) AS
Payment

      FROM Invoices INNER JOIN Vendors

             ON Invoices.VendorID = Vendors.VendorID

      WHERE InvoiceTotal > 10000.00

UNION

      SELECT InvoiceNumber, VendorName, '50% Payment' AS PaymentType,

             InvoiceTotal AS [Invoice Total],

             CONVERT(varchar, (InvoiceTotal * 0.5), 1) AS Payment

      FROM Invoices INNER JOIN Vendors

             ON Invoices.VendorID = Vendors.VendorID

      WHERE InvoiceTotal BETWEEN 500 AND 10000.00

UNION

      SELECT InvoiceNumber, VendorName, '100% Payment' AS PaymentType,

             InvoiceTotal AS [Invoice Total], (InvoiceTotal * 1.0) AS
Payment

      FROM Invoices INNER JOIN Vendors

             ON Invoices.VendorID = Vendors.VendorID

      WHERE InvoiceTotal < 500.00

ORDER BY PaymentType DESC, VendorName, InvoiceNumber

 

This UNION query calculates a payment based on what range the InvoiceTotal
happens to fall in.

 

Here are the fields from the first record of the result set.

 

InvoiceNumber: 509786

Vendorname: Bertelsmann Industry Services, Inc.

PaymentType: 50% Payment

Invoice Total: 6940.25

Payment: 3470.1250000

 

In the Invoices (base table) the "InvoiceTotal" field is defined as follows:
InvoiceTotal (money, not null)

 

I modified the code (slightly) from what is given in the book.  In the
section of code that calculates a 50% payment, I used the CONVERT function
to try and modify the output display of the "Payment" calculated field.  (I
wanted the calculated value of 3470.1250000 to display as character string
3,470.13 so that it looks more "money" like.)  However, the CONVERT function
is not working as I expected.

 

Does anyone have an idea as to why the CONVERT function is not working as
expected?

 

TIA,

 

Alan C. Lawhon