[dba-SQLServer] SQL Server 2008 Display Formatting Problem

Alan Lawhon lawhonac at hiwaay.net
Sun May 27 23:33:50 CDT 2012


Stuart:

Thanks for the link (and the tip).  The following [modified] code block
worked nicely.

  SELECT InvoiceNumber, VendorName, '50% Payment' AS PaymentType,
         InvoiceTotal AS [Invoice Total],
         '$' + CONVERT(varchar, (InvoiceTotal * CAST(0.5 As money)), 1)
         AS Payment      
  FROM Invoices INNER JOIN Vendors
         ON Invoices.VendorID = Vendors.VendorID
  WHERE InvoiceTotal BETWEEN 500.00 AND 10000.00

It's neat sticking a CAST function inside a CONVERT function.  I wonder if
this is the first time such a thing has ever been done?  (Probably not!)
Ha! Ha!

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 6:07 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] SQL Server 2008 Display Formatting Problem

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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list