string to double calculation

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  March 29, 2006 at 2:34 am

  #168891

  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.

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  March 29, 2006 at 3:29 am

  #629419

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass