HomerTNachoCheese
asked on
Exporting SQL Server 2005 data to CSV: Decimal values inaccurate (such as 4.000000000001 rather than just 4)
I am trying to export a view from SQL Server 2005 to a CSV file. One of the fields, PriceBreakControllingUnitP rice, 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.Custom erItemNumb er, dbo.FS_PriceBreak.PriceBre akControll ingUnitPri ce,
dbo.FS_PriceBreak.PriceBre akQuantity From, dbo.FS_PriceBreak.PriceBre akQuantity To, dbo.FS_PriceBreak.PriceBre akPriceBas is
FROM (SELECT PriceBook, CASE WHEN [PriceBook] = 'XX 10' THEN '123456' END AS CustomerID, PriceBookItemNumber, ItemKey,
PriceBookItemKey
FROM dbo.FS_PriceBookItem
WHERE (PriceBook IN ('XX 10'))) AS SubQuery1 INNER JOIN
dbo.FS_CustomerItem ON SubQuery1.CustomerID = dbo.FS_CustomerItem.Custom erID AND
SubQuery1.ItemKey = dbo.FS_CustomerItem.ItemKe y INNER JOIN
dbo.FS_PriceBreak ON SubQuery1.PriceBookItemKey = dbo.FS_PriceBreak.PriceBoo kItemKey
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.Custom
dbo.FS_PriceBreak.PriceBre
FROM (SELECT PriceBook, CASE WHEN [PriceBook] = 'XX 10' THEN '123456' END AS CustomerID, PriceBookItemNumber, ItemKey,
PriceBookItemKey
FROM dbo.FS_PriceBookItem
WHERE (PriceBook IN ('XX 10'))) AS SubQuery1 INNER JOIN
dbo.FS_CustomerItem ON SubQuery1.CustomerID = dbo.FS_CustomerItem.Custom
SubQuery1.ItemKey = dbo.FS_CustomerItem.ItemKe
dbo.FS_PriceBreak ON SubQuery1.PriceBookItemKey
ASKER
using numeric rather than decimal appears to work in testing. Now I will try to create the SSIS package and make sure all is still well. Also used .txt format instead for the test, which should also be an OK substitute for CSV (I doubt it matters to SQL Server).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured it out. The problem is not what I thought - conversion was probably not the issue at all, but instead it was a bug (cough... feature) of Microsoft SQL Server 2005. Maybe there is a reason why exporting to CSV or TXT gives two different results. Since the database that needs this data will accept either TXT or CSV, this is OK for me.
If someone else reading this has the same problem and requires CSV, then maybe the data can be first exported to TXT then renamed to CSV.
If someone else reading this has the same problem and requires CSV, then maybe the data can be first exported to TXT then renamed to CSV.
ASKER
I will try some other data formats. Resulting file must be a CSV file.