Newbie - Errors when using CAST() and CONVERT()

Question

I am very new to programming. I am learning SQL and am using SS2008 as a starting point. Firstly,thank you for your help! It makes the process of learning for someone like myself a lot easier.

I have read other threads but their code seems a lot more complicated.

I am going to keep this as simple as possible: I am querying a database wanting to perform a sum function on a particular column.

At present, the meta data of the column is of type varchar. I understand, to be able to use the sum function, the column in question needs to be of int.

To convert the column accordingly, I thought I could use either the cast/convert functions but incur errors when doing so:

/**
CAST:Charge_Pct FROM VARCHAR -> INT
**/


SELECT CAST(Charge_Pct AS NUMERIC(7,7)) 

FROM [Main].[Share_Class_Charges]

WHERE CHARGE_PCT <> 'None'

-- Arithmetic overflow error converting varchar to data type numeric.
/**
CONVERT: Charge_Pct FROM VARCHAR -> INT
**/

SELECT CONVERT(NUMERIC(7,7),Charge_Pct) 
 FROM [Main].[Share_Class_Charges]
WHERE CHARGE_PCT <> 'None'
-- Error converting data type varchar to numeric.

I am confused by where I am going wrong and what the error messages are saying. Please could someone help me by explaining what the error messages means and what needs to be done to correct the code?

Many thanks,

Al.

Answer 1

The NUMERIC(7,7) type describes a number with 0 digits before the decimal and 7 after. This means that if you try to cast a VARCHAR as small as 10.12 you will get an overflow error.

Try this query instead:

SELECT CAST(Charge_Pct AS NUMERIC(5,2)) 
FROM [Main].[Share_Class_Charges]
WHERE CHARGE_PCT <> 'None'

This will attempt to convert the Charge_Pct column (which I assume holds percentages) into a NUMERIC type consisting of 5 total numbers with 2 coming after the decimal place.