I am trying to format numbers coming from an oracle linked server. All Numbers should be 2 decimals. These columns appear to render in SSMS when querying from a linked server.
Now, when this query's results are exported to excel, the only formats that remain correct are the results are 3 digits or more. But i need to force 2 decimals all of the time.
I have tried to do cast/converts on the sql server side but get nvarchar/numeric conversion errors
asked Nov 15 '11 at 11:30 AM in Default
This is where you are going wrong. The output in SQL will be X,XXX.XX in a nvarchar format. So when you try to convert this into Decimal, the comma (,) will not let SQL convert it to decimal
You can do it in two ways
1.Just to follow you. Otherwise, no need to convert two times
2.Just use to TO_NUMBER and then convert to decimal
You can apply the formatting(comma style) in Excel afterwards. Hope this helps.
Casting in sql would be something like
Select Cast(WK_SHIPPED_QTY AS Decimal(13,2)) AS WK_SHIPPED_QTY
From OpenQuery( ORACLESRV, 'Select
If i do this it reveals a nasty error
Error converting data type nvarchar to numeric.
answered Nov 16 '11 at 09:26 AM