David McAfee
davidmcafee at gmail.com
Thu Oct 13 12:10:57 CDT 2011
I wouldn't worry about the formatting. Let the front end (access, VB, what ever) do it. On Thu, Oct 13, 2011 at 10:02 AM, Gary Kjos <garykjos at gmail.com> wrote: > Preface this answer by saying I don't do a lot of SQL Server queries. > > In your convert statements you are converting the output of the > calculated fields to varchar. Varchar wouldn't seem to be a format > that includes formatting like dollar signs and commas or a specific > number of decimals like something like MONEY type would. If you > replaced the varchar with money would you get the result you are > looking for? On the two fields in question you also have a STR in > there which would convert the money type fields to a string before the > addition. That would then remove the money type formatting as well > wouldn't it? If you remove that str function you would also get the > similar format result as on the balance due. > > Why do you need the convert and the str functions in there at all for > those fields? They would appear to be numeric values that you are > converting to some kind of non-numeric format and then doing the > mathematic functions on them. > > GK > > On Thu, Oct 13, 2011 at 6: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 > > > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > > > -- > Gary Kjos > garykjos at gmail.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >