Exporting SQL Server 2005 data to CSV: Decimal values inaccurate (such as 4.000000000001 rather than just 4)
Posted on 2011-05-04
I am trying to export a view from SQL Server 2005 to a CSV file. One of the fields, PriceBreakControllingUnitPrice, is a float data type, which contains price information that is almost always 2 decimal places. When I execute the view, SQL Server Management Studio is showing no more than 2 decimal places. When this is exported to CSV, a value such as 8.65 becomes 8.6500000000000004 or 4.84 becomes 4.8399999999999999 . How do I fix this?
I have made multiple attempts at different CAST and CONVERT functions, but it has no effect.
I run into this problem with Access databases on occasion, and rounding functions always take care of the issue, but not this time with SQL Server.
Below is the SELECT statement without any conversion added, but this info probably is not really needed for this question.
SELECT SubQuery1.PriceBook, SubQuery1.CustomerID, dbo.FS_CustomerItem.CustomerItemNumber, dbo.FS_PriceBreak.PriceBreakControllingUnitPrice,
dbo.FS_PriceBreak.PriceBreakQuantityFrom, dbo.FS_PriceBreak.PriceBreakQuantityTo, dbo.FS_PriceBreak.PriceBreakPriceBasis
FROM (SELECT PriceBook, CASE WHEN [PriceBook] = 'XX 10' THEN '123456' END AS CustomerID, PriceBookItemNumber, ItemKey,
WHERE (PriceBook IN ('XX 10'))) AS SubQuery1 INNER JOIN
dbo.FS_CustomerItem ON SubQuery1.CustomerID = dbo.FS_CustomerItem.CustomerID AND
SubQuery1.ItemKey = dbo.FS_CustomerItem.ItemKey INNER JOIN
dbo.FS_PriceBreak ON SubQuery1.PriceBookItemKey = dbo.FS_PriceBreak.PriceBookItemKey