Coverting a VarChar to an Integer

[TomR100]

I would like to know how to convert a Varchar fiedl to a numeric field so I can sum that field up

Rate is set to a VarChar (13).
Here is what I am trying.
select [Employee],
DomicileTerminal,
ExLawson.[JobCode],
[PayCode],
[Hours],
SUM(CONVERT(INT, Rate))AS Rate
from [tmpExportLaw] AS ExLaw
JOIN tmpEmployeeMaster AS EmpMast ON (ExLaw.Employee = EmpMast.EmployeeID)
WHERE DomicileTerminal IN
('AL','AY','BA','HA','PH','RD','SY','WB')
AND PayCode NOT IN ('70', '99')
GROUP BY Employee, DomicileTerminal, ExLaw.JobCode, PayCode, Hours
ORDER BY Employee

Here is the message I get:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '269.78' to a column of data type int.

Can anyone help?

Thank you,
TomR100

 

[SQLSister]

269.78 is a decimal not an integer, so it can't convert it.

Questions about posting. See faq183-874

 

[SQLBill]

SQLSister explained what the problem is, here is the solution:

SUM(CONVERT(DECIMAL(5,2), Rate))AS Rate

Of course you will have to set the precision and scale to appropriate values. See the BOL for more info on DECIMAL.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine