Stuart McLachlan
stuart at lexacorp.com.pg
Sat May 26 18:06:53 CDT 2012
I didn't think it through far enough. It is not the datatype of Invoice Total that is important, it is the datatype of (InvoiceTotal * 0.5): http://dba.fyicenter.com/faq/sql_server/Rules_on_Arithmetic_Operations.html When arithmetic operations are performed on expressions of different data types, implicit data type conversion will be performed before the arithmetic operation on the expression with a lower data type rank. Numeric data type ranks are from low to high: TINYINT, SMALLINT, INT, BIGINT, SMALLMONEY, MONEY, DECIMAL, and FLOAT IOW, multiplying InvoiceTotal (Money) by 0.5 (Float) results in an implicit conversion to a Float, hence the "1" converting to 8 digits. -- Stuart On 26 May 2012 at 17:49, Alan Lawhon wrote: > Stuart: > > The following is from my initial post: > > In the Invoices (base table) the "InvoiceTotal" field is defined as follows: > InvoiceTotal (money, not null) > > Alan C. Lawhon > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart > McLachlan > Sent: Saturday, May 26, 2012 5:13 PM > To: Discussion concerning MS SQL Server > Subject: Re: [dba-SQLServer] SQL Server 2008 Display Formatting Problem > > A lot depends on what type InvoiceTotal is: Decimal, Float, Money, > SmallMoney? > You will get different results when converting different types to VARCHAR. > > Based on your output, it looks as though InvoiceTotal is a Float, which "1" > will always convert to 8 digits. > > > -- > Stuart > > On 26 May 2012 at 13:47, Alan Lawhon wrote: > > > 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 > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > -- > Stuart McLachlan > > Ph: +675 340 4392 > Mob: +675 7100 2028 > Web: http://www.lexacorp.com.pg > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- Stuart McLachlan Ph: +675 340 4392 Mob: +675 7100 2028 Web: http://www.lexacorp.com.pg