asked on

formatting float data types in queries

i have a number (float) in an sql table when displayed in my flex grid shows about 8 decimals places. wondering if its my flex grid property, ...or can i format/ convert my SQL query to format the number to now show more than two decimal places ?

SQL query=

SELECT dbo.tePremPurchaseLines.LineNumber, dbo.tePremPurchaseLines.TaxInclusiveTotal, dbo.tePremPurchaseLines.Quantity,
dbo.tePremPurchaseLines.TaxInclusiveUnitPrice, dbo.tePremPurchaseLines.Discount, dbo.tItems.ItemNumber,
dbo.tePremPurchaseLines.PurchaseID
FROM dbo.tePremPurchaseLines INNER JOIN
dbo.tItems ON dbo.tePremPurchaseLines.ItemID = dbo.tItems.ItemID
WHERE (dbo.tePremPurchaseLines.PurchaseID = '35')

the offending fields are TaxInclusiveTotal & TaxInclusiveUnitPrice

thanks, peter.

Anthony Perkins

You can do this two ways with T-SQL:

1. CAST the float columns to Numeric(18, 2)
2. Change the data type of the float columns to (18, 2)

stromberg45

ASKER

can you show me how to do this in the SQL language, i.e. put it into my query

thanks.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stromberg45

ASKER

i can't change in table, it must be stored as float, because it's currency i'm dealing with.

thankyou for your help.the above solution worked very well.

Anthony Perkins

>>i can't change in table, it must be stored as float, because it's currency i'm dealing with.<<
If you cannot change the data type to a more appropriate data type (such as money or numeric) than make sure you allow for rounding problems, such as such as 123.45 store as 123.4599999999. Otherwise it can be a real nightmare.

The only time you should be using float with currency is if you are dealing with the National Deficit and you need very large numbers, otherwise you are heading for trouble very fast. The Money data type is an 8 byte value and reaches a maximum of 922,337,203,685,477.5807.

Anthony Perkins

Typo, this:
such as 123.45 store as 123.4599999999

Should have read:
such as 123.45 store as 123.4499999999