BCP implicit conversion question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BCP implicit conversion question

Author

Topic

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-03 : 13:17:50

Hello;

I export data successfully to excel with this query..

DECLARE @cmd VARCHAR(2048)
SET @cmd = 'bcp '
+ ' "SELECT Manufacturer,Cost,Price," '
+ ' FROM MyBb..MyTable " queryout'
+ ' D:\Excel_Exports\myfile.csv -c -t\, -r \n -T'

Cost and Price however sometimes report back with several decimals of precision in the csv file. For example in the table they may be 2.9 but in the csv it will show 2.89999999999. The tables are setup to only have 2 decimals of precision on insert so I do not know why the values are coming back like 2.899999 etc..

Is there a way to preserve the value on export to csv without getting all the extra decimals? Perhaps one of the switches needs to be adjusted?

thanks for any help !

R&R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:21:49

try casting them explicitly to decimal in query


DECLARE @cmd VARCHAR(2048)
SET @cmd = 'bcp '
+ ' "SELECT Manufacturer,CAST(Cost as decimal(10,1)),CAST(Price AS decimal(10,1))," '
+ ' FROM MyBb..MyTable " queryout'
+ ' D:\Excel_Exports\myfile.csv -c -t\, -r \n -T'

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-03 : 15:24:44

yep, that did the trick.

thx !

r&r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources