I have a field cost with values 0.987878656435798654 , 0.765656787898767
I am trying to figure out what would be the datatype for this.
When I give decimal 15,15 and trying to load data it is throwing me an error
Arithmetic overflow error converting varchar to data type numeric.
The problem is that you are not allocating any length to the value before the decimal.
DECIMAL (15, 15) means that it has a precision of 15 digits after the decimal, but only enough room for 15 digits total - thus leaving no room for values greater than 1.
This means that DECIMAL (15, 15) only supports values in the following range:
-0.999999999999999 to 0.999999999999999 (15 digits after the decimal).
You have 18 digits in your first example, so I would recommend using something like DECIMAL (21, 18)
DECIMAL (21, 18) will support values in the range from: -999.999999999999999999 to 999.999999999999999999 (18 digits after the decimal).
But, you should analyze your own data to see what the maximum value would be that you need to support.
DECIMAL(28,25) (for example), gaining more precision with the same amount of storage bytes (source: msdn.microsoft.com/en-us/library/ms187746.aspx). You may also want to consider using money types: msdn.microsoft.com/en-us/library/ms179882.aspx, although it only has up to 4 decimal digits (usually more than enough for money, maybe not your case).
Try this...
SELECT LEN(YourColumn)
FROM YourTable
Then , if they are below 1 every time, try this...
SELECT CONVERT(DECIMAL(X,X-1),YourColumn)
Where X is what is returned in the LEN statement. and X-1 is one less than that.
Remember, it's DECIMAL(TotalLength,Precision) so you need to make sure have enough space for the total value.
