[dba-SQLServer] Converting an Alias (Calculated Field) of Money Data Type to Display as varchar

It is because you are converting them to varchars.

If you leave them as money, you should be fine.

If you want to use the formatting that STR provides, convert it to money
first, like I did in Test2:


DECLARE @InvoiceNumber AS int  SET @InvoiceNumber = 12345
DECLARE @InvoiceTotal AS MONEY SET @InvoiceTotal  = 1999.99
DECLARE @PaymentTotal AS MONEY SET @PaymentTotal  = 20
DECLARE @CreditTotal AS MONEY  SET @CreditTotal   = 5

DECLARE @TEN_PERCENT AS money;

DECLARE @INVOICE_PLUS_TEN AS money;

SELECT @InvoiceNumber AS Number,
      CONVERT(varchar, @InvoiceTotal, 1) AS [Invoice Total],
--
--     The three following fields are all alias calculated fields.
--
      CONVERT(varchar, STR(@InvoiceTotal * 0.10, 10, 2), 1) AS TEN_PERCENT,
        CONVERT(varchar, STR((@InvoiceTotal + (@InvoiceTotal * 0.10)), 10,
2),1) AS INVOICE_PLUS_TEN,
        CONVERT(varchar, CAST(STR((@InvoiceTotal + (@InvoiceTotal * 0.10)),
10, 2) AS MONEY) ,1) AS INVOICE_PLUS_TEN2,
        CAST(@InvoiceTotal + (@InvoiceTotal * 0.10)AS MONEY) AS
INVOICE_PLUS_TEN_TEST3,
        (@InvoiceTotal + (@InvoiceTotal * 0.10)) AS INVOICE_PLUS_TEN_TEST4,

--
      CONVERT(varchar, (@InvoiceTotal - (@PaymentTotal + @CreditTotal)), 1)
AS [Balance Due]





On Thu, Oct 13, 2011 at 4:35 AM, Alan Lawhon <lawhonac at hiwaay.net> wrote:

> I've played around with this one to the point where it's driving me nuts.
> The code snippet follows (along with two records from the result set)
> followed by my question.
>
>
>
> NOTE:  Base table fields "InvoiceTotal, PaymentTotal, and CreditTotal" are
> all of data type (money, not null)
>
>
>
> USE AP
>
> GO
>
> DECLARE @TEN_PERCENT AS money;
>
> DECLARE @INVOICE_PLUS_TEN AS money;
>
> SELECT InvoiceNumber AS Number,
>
>       CONVERT(varchar, InvoiceTotal, 1) AS [Invoice Total],
>
> --
>
> --     The three following fields are all alias calculated fields.
>
> --
>
>       CONVERT(varchar, STR(InvoiceTotal * 0.10, 10, 2), 1) AS TEN_PERCENT,
>
>       CONVERT(varchar, STR((InvoiceTotal + (InvoiceTotal * 0.10)), 10, 2),
> 1) AS INVOICE_PLUS_TEN,
>
> --
>
>       CONVERT(varchar, (InvoiceTotal - (PaymentTotal + CreditTotal)), 1) AS
> [Balance Due]
>
> --
>
> FROM Invoices
>
> WHERE InvoiceTotal - (PaymentTotal + CreditTotal) > 1000
>
> ORDER BY InvoiceTotal DESC
>
>
>
> Here are two records from the result set:
>
>
>
>      Number      InvoiceTotal      TEN_PERCENT     INVOICE_PLUS_TEN
> Balance Due
>
> 1    P-0608         20,551.18             2055.12                  22606.30
> 19,351.18
>
> 2    0-2436         10,976.06             1097.61                  12073.67
> 10,976.06
>
>
>
> OK, here's my question.  In the display output for the TEN_PERCENT and
> INVOICE_PLUS_TEN calculated fields, why do those values display without the
> comma character while the values for the InvoiceTotal and Balance Due
> fields
> both display with the comma?  (In other words, the "style = 1" argument in
> the CONVERT function seems to work just fine when converting base table
> [money] fields to varchar data type, but not when attempting to convert a
> calculated alias field value.)
>
>
>
> This is nitpicking (I know), but I get wrapped around the axle on things
> like this.
>
>
>
> Alan C. Lawhon
>