CODEWITHSUNDEEP
t-sqlexportssms
disasterkid
disasterkid

Reputation: 7278

Decimals less than 1 appear as ",x" in output file while they appear correctly in the result window

I am having difficulty with my decimal columns. I have defined a view in which I convert my decimal values like this

E.g.

SELECT CONVERT(decimal(8,2), [ps_index]) AS PriceSensitivityIndex

When I query my view, the numbers appear correctly on the results window e.g. 0,50, 0,35.

However, when I export my view to file using Tasks > Export Data ... feature of SSMS, the decimals lower than zero appear as ,5, ,35.

How can I get the same output as in the results window?

Upvotes: 1

Views: 217

Answers (1)

jamie
jamie

Reputation: 775

Change your query to this:

SELECT CAST( CONVERT(decimal(8,2), [ps_index]) AS VARCHAR( 20 ) ) AS PriceSensitivityIndex

Not sure why, but bcp is dropping leading zero. My guess is it's either because of the transition from SQL Storage to a text file. Similar to how the "empty string" and nulls are exchanged on BCP in or out. Or there is some deeper config (windows, sql server, ?) where a SQL Server config differs from an OS config? Not sure yet. But since you are going to text/character data anyway when you BCP to a text file, it's safe (and likely better in most cases) to first cast/convert your data to a character data type.

Upvotes: 1

Related Questions