Exporting Floating Table data in SQL Server 2008 R2

Question

I'm trying to export a database table with some nvarchar (not related to the actual problem) and float columns (where the problem occurs) to a flat file.

The issue is that when I use the Import/Export Wizard in SQL Server 2008 R2 x64 in order to export the table to a flat file, the float columns get some "weird" behaviour related to rounding/precision.

For example, if the "actual" value stored in the column is 137691869,04 then in the exported file I get 137691869,03999999 instead of 137691869,04.

How can I resolve that problem in order to get the "exact" value in the exported flat file?

Thanks!

Answer 1

You should use round() when exporting to truncate:

SELECT ROUND(137691869.03999999, 2) 

Will return the value 137691869.04

If you don't like rounding in your version of server, you could try other related functions: TRUNC(), NUMERIC(), DECIMAL()

I does not matter how the number is stored, round affects only the output:

SELECT ROUND(137691869.04, 2) 

Will return the value 137691869.04