March 29, 2006 at 2:34 am
i get an error when executing ,
Select sum(ToDouble(Jan_Hours)) from employeepayments
where Employer_Number = '2346' and [Year] = '2005'
the Jan_Hours is an varchar type of column.
how to get sum(Jan_Hours) + sum(Feb_hours) +
i also need sum (sum(Jan_Hours) + sum(Feb_hours))
Query ::
Select sum(convert(decimal(10,2), Jan_Hours))
from employeepayments
where Employer_Number = '2346' and [Year] = '2005'
Error ::
Server: Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
i am working on imported database , so i cannot chnage the datatype of Jan_Hours to int.
March 29, 2006 at 3:29 am
Most likely you've got some non numeric values in the field. See what this returns
SELECT Jan_Hours FROM employeepayments WHERE ISNUMERIC(Jan_Hours) = 0
It's not perfect, isnumeric sometimes returns true for values that can't be cast to a numeric type, but should pick up most.
As for the sum of varying months, if you have a column Jan_Hours and a column Feb_Hours, then
SELECT (SUM(Jan_Hours) + SUM(Feb_Hours)) AS TotalOfJanAndFeb FROM employeepayments WHERE ...
This will also work (but watch out for nulls)
SELECT SUM(Jan_Hours + Feb_Hours) AS TotalOfJanAndFeb FROM employeepayments WHERE ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply