[dba-SQLServer] SQL Server 2008 Display Formatting Problem

Alan Lawhon lawhonac at hiwaay.net
Sat May 26 13:47:56 CDT 2012

I (sort of) figured out the problem.  Solely on a hunch, I decided to
comment out all of the code except for the block of statements that handle
the 50 percent calculation.  (The line with the CONVERT function was the
line producing the compile error, so it made sense to isolate on that block
of code.)  I then ran the code with the CONVERT function still in place to
calculate (and display) the "Payment" field.  The modified code appears as

    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
ORDER BY PaymentType DESC, VendorName, InvoiceNumber

Interestingly, this code executed (no compile error) so the CONVERT function
worked  although the style parameter didn't produce the exact result that
the SQL Server documentation indicates.  The:

   '$' + CONVERT(varchar, (InvoiceTotal * 0.5), 1) AS Payment

field calculation produced output that looked like $1062.12500 when it
should have appeared as: $1,062.13.  Maybe I'm a bit confused as to how
precision and scale work when dealing with decimal and money data types.  It
might also be the case that when you have a UNION query, SQL Server doesn't
play nice with the CONVERT function.

This is nitpicking anyway, so I'm going to let it go and move on to the next

Alan C. Lawhon

More information about the dba-SQLServer mailing list