[dba-SQLServer] SQL Server 2008 Display Formatting Problem

Alan Lawhon lawhonac at hiwaay.net
Sat May 26 17:49:56 CDT 2012


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



More information about the dba-SQLServer mailing list