SQLTeam.com | Weblogs | Forums

Export a flat file with pipe delimited not working with price

Hello, I'm trying to export a database of mine and that's a flat file and pipe delimited issues is that the price is messed up. The price field data type is float and it looks like this after exporting:

011105020015|COLTRANE,JOHN|CRESCENT|LP|25.989999999999998|1

Should look like this:

011105020015|COLTRANE,JOHN|CRESCENT|LP|25.99|1

I've tried changing the mapping type to numeric and other things all so far do the same thing. What am I batching up? I'm Using SSMS export wizard.

Thanks

More info:

row delimiter is {CR}{LF}
column delimiter is Vertical Bar {|}

FLOAT and REAL data type are approximate representation of a number. So, what you submitted as 25.99 to the database may be represented internally as 25.989999999999998. This is the reason you are seeing this behavior.

This is not something specific to SQL Server. Pretty much all the programming environments and databases support approximate numerics, and they all behave this way.

If you need it to be consistently what you expect, use or convert to one of the exact numeric data types.