[dba-SQLServer] SQL Server 2008 Display Formatting Problem

Alan,

The problem is that all elements of a union query have to be the same 
data type.
You were mixing numeric and character data.  You would have to use the convert
on all the union sections for the money to not have the error.

As to the behavior of convert on money.  Did you look at the value 
type for money?
It goes to 4 decimals.  So, it is working perfectly correct.  You 
would need to either
ROUND or Truncate the last 2 digits of the money amount.

Robert

At 04:59 PM 5/26/2012, you wrote:
>Date: Sat, 26 May 2012 13:47:56 -0500
>From: "Alan Lawhon" <lawhonac at hiwaay.net>
>To: "'Discussion concerning MS SQL Server'"
>         <dba-sqlserver at databaseadvisors.com>
>Subject: Re: [dba-SQLServer] SQL Server 2008 Display Formatting
>         Problem
>Message-ID: <000101cd3b70$10930960$31b91c20$@net>
>Content-Type: text/plain;       charset="us-ascii"
>
>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
>follows:
>
>USE AP
>GO
>     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
>topic.
>
>Alan C. Lawhon
>
>

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com