Newbie - Errors when using CAST () and CONVERT ()
I am very new to programming. I am learning SQL and using SS2008 as a starting point. First, thanks for your help! This makes learning much easier for someone like me.
I've read other threads, but their code looks a lot more complex.
I'm going to keep it as simple as possible: I'm querying a database that wants to execute a sum function on a specific column.
Column metadata is currently varchar. I understand to be able to use the sum function, the corresponding column must be int.
In order to convert the column accordingly, I thought I could use either the cast / convert functions, but make mistakes:
/**
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 as to where I am going wrong and what the error messages say. Please help me by explaining what the error messages mean and what should be done to fix the code?
Many thanks,
Al.
source to share
The type NUMERIC(7,7)
describes a number with 0 digits before decimal and 7 after. This means that if you try to make a a VARCHAR
size 10.12
, you will get an overflow error.
Try this query:
SELECT CAST(Charge_Pct AS NUMERIC(5,2))
FROM [Main].[Share_Class_Charges]
WHERE CHARGE_PCT <> 'None'
This will try to convert the column Charge_Pct
(which I believe contains percentages) to a type NUMERIC
consisting of 5 full numbers with 2 following the decimal point.
source to share