Getting error when trying to convert result to numeric

Question

Getting error when trying to convert result to numeric

naresh0407 93367

SSC-Addicted

Points: 479
More actions
November 9, 2010 at 9:21 pm

#149300

Hi All,
I am getting erro in stored procedure
I am trying convert result into Numeric(10,2) to round off the value an getting erro.
Error Message
Msg 8114, Level 16, State 5, Procedure SAR_Sp_Schedule, Line 10
Error converting data type varchar to numeric.
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]
ALTER procedure [dbo].[SAR_Sp_Schedule](@WeekStartDate datetime,@CampaignID int)
as
Begin
Declare @TotalEmp int
Declare @EndDate datetime
Declare @StartDate datetime
set @StartDate=dateadd(day,-7,dateadd(day,-7,dateadd(day,-7,@WeekStartDate)))
set @EndDate=dateadd(day,6,@WeekStartDate)
set @TotalEmp=46
Select 'Less Than 25 Hrs' as [Scheduled Hrs],
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='10/3/2010'),@TotalEmp)*100)+'%' as [Second Week],
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='10/10/2010'),@TotalEmp)*100)+'%' as [Third Week],
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]
union all
Select 'Between 25 Hrs and 30 Hrs' as [Scheduled Hrs],
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='10/3/2010'),@TotalEmp)*100)+'%' as [Second Week],
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='10/10/2010'),@TotalEmp)*100)+'%' as [Third Week],
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]
union all
Select 'Greater than 30.10' as [Scheduled Hrs],
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/3/2010'),@TotalEmp)*100)+'%' as [Second Week],
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/10/2010'),@TotalEmp)*100)+'%' as [Third Week],
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]
End

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

Can you just post result of the below Query to see what value you are converting to numeric

dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 2

WHen i run the query i will rsult like 98.66% or 97.89% etc.This i am trying to round off with numeric datatype.

Instead of using varchar i am trying to use numeric

convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]