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

Alan Lawhon lawhonac at hiwaay.net
Thu Oct 13 06:35:06 CDT 2011


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

  




More information about the dba-SQLServer mailing list